I have created a table with 2 columns (Myfilename varchar(255) and the myPicture Varbinary(max)).
I was able to upload a file into the table using Openrowset(Bulk ‘E:\temp\myTest.jpg’,SINGLE_BLOB) AS [file]… worked like a charm.
I now, need to retrieve the picture from the table and put in a jpeg file using TSQL. I have been searching everywhere without any success…
Would you have any idea?
It would be greatly appreciated…
writing the data out is more tricky. You could use bcp and the command shell like:
declare @SQLcommand nvarchar(4000)
set @SQLcommand = 'bcp "SELECT data FROM tempdb..binary_data WHERE id = ''c:\data.jpeg'' " queryout "c:\data.jpeg" -T -N -Sservername'
exec xp_cmdshell @SQLcommand
(replace the servername, and if not using windows authentication you will have to provide the credentials to connect to the database)
Andras Belokosztolszki, MCPD, PhD