Export the image from the SQL Server table to the folder using Sql script

In the previous post we saw how we can import the data into the table using sql script.

Read Here: Insert the image into the SQL Server table using Sql script

https://bageshkumarbagi-msbi.blogspot.com/2021/06/insert-image-into-sql-server-table.html

In this post we will see how to export the image from the sql server table to the folder using sql script.

Before using the script we need to enable below setting into the server level.

USE MASTER GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO -- Add 'bulkadmin' to the correct user ALTER SERVER ROLE [bulkadmin] ADD MEMBER [NT AUTHORITY\SYSTEM] GO

 

We can export the image from the database table using the SSIS package.

Read here:  Export the Image from databaseusing SSIS

https://bageshkumarbagi-msbi.blogspot.com/2021/03/export-image-from-database-using-ssis.html

In this port we will see how to export the image from the table.

We are exporting the photo on the below folder.    

Using below script to export the image.

DECLARE @ThumbnailPhotoFileName             NVARCHAR(100)='Bus_small.jpg',         @LargePhotoFileName                 NVARCHAR(100)='Bus.jpg',         @Output_path                        NVARCHAR(500)=         'H:\SSIS1\Photo\Output',         @ThumbnailPhoto_File_path           NVARCHAR(500),         @LargePhoto_File_path               NVARCHAR(500),         @ThumbnailPhotoFileName_Binary_data VARBINARY (max),         @LargePhotoFileName_Binary_data     VARBINARY (max),         @OutputPath                         NVARCHAR(4000),         @Obj                                INT   SELECT @ThumbnailPhotoFileName_Binary_data = (SELECT               CONVERT(VARBINARY(max), thumbnailphoto, 1)                                               FROM   production.productphoto                                               WHERE               thumbnailphotofilename = @ThumbnailPhotoFileName);   SELECT @LargePhotoFileName_Binary_data = (SELECT        CONVERT(VARBINARY(max), largephoto, 1)                                           FROM   production.productphoto                                           WHERE               thumbnailphotofilename = @ThumbnailPhotoFileName);   SET @ThumbnailPhoto_File_path = Concat(@Output_path, '\', @ThumbnailPhotoFileName); SET @LargePhoto_File_path = Concat(@Output_path, '\', @LargePhotoFileName);   BEGIN try     --exporting the Thumbnail Photo File deatils     EXEC Sp_oacreate       'ADODB.Stream',       @Obj output;       EXEC Sp_oasetproperty       @Obj,       'Type',       1;       EXEC Sp_oamethod       @Obj,       'Open';       EXEC Sp_oamethod       @Obj,       'Write',       NULL,       @ThumbnailPhotoFileName_Binary_data;       EXEC Sp_oamethod       @Obj,       'SaveToFile',       NULL,       @ThumbnailPhoto_File_path,       2;       EXEC Sp_oamethod       @Obj,       'Close';       EXEC Sp_oadestroy       @Obj;       --exporting the Large Photo File deatils     EXEC Sp_oacreate       'ADODB.Stream',       @Obj output;       EXEC Sp_oasetproperty       @Obj,       'Type',       1;       EXEC Sp_oamethod       @Obj,       'Open';       EXEC Sp_oamethod       @Obj,       'Write',       NULL,       @LargePhotoFileName_Binary_data;       EXEC Sp_oamethod       @Obj,       'SaveToFile',       NULL,       @LargePhoto_File_path,       2;       EXEC Sp_oamethod       @Obj,       'Close';       EXEC Sp_oadestroy       @Obj; END try   BEGIN catch     EXEC Sp_oadestroy       @Obj; END catch

Now running this script    

Script executed successfully.

See the files in the folder.   

Thanks for Reading.