Script to save varbinary data to disk

by

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

More Related Contents:

  1. Insert an image in a column of a table that already has 5 columns
  2. Get top 1 row of each group
  3. Referring to a Column Alias in a WHERE Clause
  4. How to SELECT FROM stored procedure
  5. Convert Month Number to Month Name Function in SQL
  6. How to avoid the “divide by zero” error in SQL?
  7. SET versus SELECT when assigning variables?
  8. Is there any difference between IS NULL and =NULL
  9. Generate a resultset of incrementing dates in TSQL
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. Getting result of dynamic SQL into a variable for sql-server
  12. How to get difference between two rows for a column field?
  13. Better techniques for trimming leading zeros in SQL Server?
  14. What’s the best way to select the minimum value from several columns?
  15. Concatenate values based on ID
  16. Splitting delimited values in a SQL column into multiple rows
  17. varbinary to string on SQL Server
  18. SQL : in clause in stored procedure:how to pass values
  19. How to insert a blob into a database using sql server management studio
  20. SQL Server converting varbinary to string
  21. SQL GROUP BY CASE statement with aggregate function
  22. separate comma separated values and store in table in sql server
  23. Best way to do nested case statement logic in SQL Server
  24. SQL Server 2005 ROW_NUMBER() without ORDER BY
  25. Can an INNER JOIN offer better performance than EXISTS
  26. How to check which locks are held on a table
  27. How to compare dates in SQL Server
  28. How do I expand comma separated values into separate rows using SQL Server 2005?
  29. Is there a quick way to check if ANY column is NULL?
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment