Script to save varbinary data to disk

Question

I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is returned) and outputs the bytes to disk (one file per row?) I'm sure this has been asked a thousand times before, but Googling comes up with mostly .net solutions. I want an SQL solution.

Answer 1

The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren't equivalent to what's stored. Perhaps BCP is writing some kind of header. I'm not sure.

I found the following code here at the bottom of the article. It works great! Although it was intended for stored BMP images, it works with any varbinary.

DECLARE @SQLIMG VARCHAR(MAX),     @IMG_PATH VARBINARY(MAX),     @TIMESTAMP VARCHAR(MAX),     @ObjectToken INT  DECLARE IMGPATH CURSOR FAST_FORWARD FOR          SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations  OPEN IMGPATH   FETCH NEXT FROM IMGPATH INTO @IMG_PATH   WHILE @@FETCH_STATUS = 0     BEGIN         SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'          PRINT @TIMESTAMP         PRINT @SQLIMG          EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT         EXEC sp_OASetProperty @ObjectToken, 'Type', 1         EXEC sp_OAMethod @ObjectToken, 'Open'         EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH         EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2         EXEC sp_OAMethod @ObjectToken, 'Close'         EXEC sp_OADestroy @ObjectToken          FETCH NEXT FROM IMGPATH INTO @IMG_PATH      END   CLOSE IMGPATH DEALLOCATE IMGPATH