I'm in trouble. I need copy a file from one server to another using T-sql. The format is variable (pdf, xlsx, doc, jpg), but generally excel .xlsx.
For now my solution is to convert on the source server the file in a varbinary data and pass it as a parameter to a stored procedure located on the target server.
The code in the source server:
DECLARE @temp table(data varbinary (max))
DECLARE @out varbinary(max)
INSERT INTO @temp(data)
SELECT * FROM OPENROWSET(BULK N'C:\original.xlsx', SINGLE_BLOB) as data
SELECT @out=data FROM @temp
EXEC DEST_SERVER.copy @out
(I am aware that the code is ugly and redundant, but so far going well)
The code on the destination server is:
CREATE PROCEDURE copy @in varbinary(max)
AS
BEGIN
CREATE TABLE ##temp(data varbinary(max))
insert into ##temp(data) values(@in)
declare @SQLcommand varchar(1000)
set @SQLcommand = 'bcp "SELECT data FROM ##temp" queryout "C:\copied.xlsx" -T -S ' + @@SERVERNAME + ' -n'
exec xp_cmdshell @SQLcommand
DROP TABLE ##temp
END
By the time the procedure works to create the new file on the destination server, but both files are slightly different.
Specifically, the new file has a few more bits at the beginning, everything else is identical:
original.xlsx
50 4B 03 04 14 00 06 00
08 00 00 00 21 00 A7 95
F9 99 84 01 00 00 14 06
00 00 13 00 DD 01 5B 43
6F 6E 74 65 6E 74 5F 54
etc...
copied.xlsx
4F 32 00 00 00 00 00 00 -> this bytes are new
50 4B 03 04 14 00 06 00
08 00 00 00 21 00 A7 95
F9 99 84 01 00 00 14 06
00 00 13 00 DD 01 5B 43
6F 6E 74 65 6E 74 5F 54
etc...
What am I doing wrong ?,
The guy in this post had the same problem. He fix it by specifying a format file with the bcp command. I think this might be your problem too. It looks like it might have something to do with bcp and a varbinary column.
SQL Server BCP export corrupted file?
Hope that helps