Extract images from database by serverku

As we learned for the import the images into the database from folder in the last post. One more thing is also interesting to learn here as how can we extract images from database.

SP_CONFIGURE  'Ole Automation Procedures',1
GO
RECONFIGURE
GO

SELECT * FROM [IMAGEINSERT]
GO

DECLARE @Image VARBINARY(MAX)
SELECT @Image = (SELECT [Image] FROM [IMAGEINSERT] )
DECLARE @ObjectImage INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectImage OUTPUT
EXEC sp_OASetProperty @ObjectImage, 'Type', 1
EXEC sp_OAMethod @ObjectImage, 'Open'
EXEC sp_OAMethod @ObjectImage, 'Write', NULL, @Image
EXEC sp_OAMethod @ObjectImage, 'SaveToFile', NULL, 'D:\image\ImageExport.jpg', 2
EXEC sp_OAMethod @ObjectImage, 'Close'
EXEC sp_OADestroy @ObjectImage

Delete files using Ole Automation Procedures in SQL Server by serverku

To delete the files using Ole Automation Procedures in SQL Server we need to first enable 'Ole Automation Procedures' using sp_configure as following,

exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

After enabling it, we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name 'DeleteFile' in the tsql script. You can delete all the files or specific files as well.

DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'D:\TestFolder\*.txt'
EXEC @Result = sp_OADestroy @FSO_Token