Friday, May 31, 2013

How to update BLOB field in SQL Server?

UPDATE myTable
SET theBlobField =
(SELECT BulkColumn FROM OPENROWSET (BULK 'C:\logo.png, SINGLE_BLOB) a)
WHERE [ID] = 1

How to save binary (BLOB) field into local file from SQL Server database by SQL?



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


Reference:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754