By: Eli Leiba | Last Updated: 2017-07-17 | Comments (12) | Import and Export
The requirement is to be able to either import or export an image (binary) file to or from SQL Server without using third party tools and without using the BCP (Bulk Copy Program) utility or Integration Services (SSIS). The goal is to use only the database engine capabilities using simple T-SQL code.
The solution involves a table that stores image data and the programming of two stored procedures. The first procedure does the import of the image file into a SQL table and the second procedure does the export of the image from a SQL table.
Both procedures have the same three parameters:
- @PicName - This is a unique key that defines the picture record. Note that the import action assumes that this is a new picture so only inserting is allowed.
- @ImageFolderPath - For the export, this is the folder where the file would be saved. For the import, this is the folder where the file is imported from. Note this folder should exist on your SQL Server and not on a client machine.
- @Filename - For the export, it is the name of the output file and for the import it is the name of the input file.
The import procedure uses the OPENROWSET function combined with the BULK option to import the file into SQL Server. Since this comes from a parameter, the statement is executed dynamically using the SQL EXEC function using dynamic SQL.
The export procedure uses SQL Server's OLE Automation Procedures ability to write the selected image data stored in a large varbinary variable found by selecting the data by querying the pictures table by the picture name and then saving it to a file in the OS by using the internal sp_OAMethod system procedure.
Create Table and Stored Procedures
In order to store the image file inside SQL Server, I have a simple table called dbo.Pictures containing the picture name, the picture file name and the binary data of the picture itself.
Here is the Pictures table creation script:
CREATE TABLE Pictures ( pictureName NVARCHAR(40) PRIMARY KEY NOT NULL , picFileName NVARCHAR (100) , PictureData VARBINARY (max) ) GO
Please note that as a preliminary action the OLE Automation Procedures option must be set and active on the SQL Server for the image export action and the BulkAdmin privilege should be given to the executor of the image import action.
Here is the T-SQL script needed for those privileges:
Use master Go EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO ALTER SERVER ROLE [bulkadmin] ADD MEMBER [Enter here the Login Name that will execute the Import] GO
Image Import Stored Procedure
CREATE PROCEDURE dbo.usp_ImportImage ( @PicName NVARCHAR (100) , @ImageFolderPath NVARCHAR (1000) , @Filename NVARCHAR (1000) ) AS BEGIN DECLARE @Path2OutFile NVARCHAR (2000); DECLARE @tsql NVARCHAR (2000); SET NOCOUNT ON SET @Path2OutFile = CONCAT ( @ImageFolderPath ,'\' , @Filename ); SET @tsql = 'insert into Pictures (pictureName, picFileName, PictureData) ' + ' SELECT ' + '''' + @PicName + '''' + ',' + '''' + @Filename + '''' + ', * ' + 'FROM Openrowset( Bulk ' + '''' + @Path2OutFile + '''' + ', Single_Blob) as img' EXEC (@tsql) SET NOCOUNT OFF END GO
Image Export Stored Procedure
CREATE PROCEDURE dbo.usp_ExportImage ( @PicName NVARCHAR (100) ,@ImageFolderPath NVARCHAR(1000) ,@Filename NVARCHAR(1000) ) AS BEGIN DECLARE @ImageData VARBINARY (max); DECLARE @Path2OutFile NVARCHAR (2000); DECLARE @Obj INT SET NOCOUNT ON SELECT @ImageData = ( SELECT convert (VARBINARY (max), PictureData, 1) FROM Pictures WHERE pictureName = @PicName ); SET @Path2OutFile = CONCAT ( @ImageFolderPath ,'\' , @Filename ); BEGIN TRY EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT; EXEC sp_OASetProperty @Obj ,'Type',1; EXEC sp_OAMethod @Obj,'Open'; EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData; EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2; EXEC sp_OAMethod @Obj,'Close'; EXEC sp_OADestroy @Obj; END TRY BEGIN CATCH EXEC sp_OADestroy @Obj; END CATCH SET NOCOUNT OFF END GO
You have a file called Dragon.jpg in the C:\MyPictures\Input folder.
In order to import to SQL Server execute the following:
exec dbo.usp_ImportImage 'DRAGON','C:\MyPictures\Input','Dragon.jpg'
The data is now inside the pictures table and looks like this, if we query the table.
In order to export the file, use the following:
exec dbo.usp_ExportImage 'DRAGON','C:\MyPictures\Output','Dragon.jpg'
The file is now exported to C:\MyPictures\Output\Dragon.jpg.
- You can create these simple procedures and table in your database and use them for handling image files (or any other binary files).
- Make sure you assign the needed privileges.
- The procedures were tested on SQL Server 2014 - 12.0.2000.8 (Intel X86) Standard Edition
Last Updated: 2017-07-17
About the author
View all my tips