Simple Image Import and Export Using T-SQL for SQL Server

By:   |   Comments (39)   |   Related: More > Import and Export


Problem

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.

Solution

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

Example Use

You have a file called Dragon.jpg in the C:\MyPictures\Input folder.

SQL import image example

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.

picture name

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.

Next Steps
  • 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, November 23, 2023 - 1:07:49 AM - Jaideep Back To Top (91771)
I have a table that contains PDF's that I want to import to a local drive or folder. All the PDF Files with their respective column name data, if possible. Want to export them to C:\files. Would you be able to suggest a script or stored procedure or anything, please?

Wednesday, May 24, 2023 - 2:50:05 PM - Jesse James Back To Top (91222)
This is freaking BRILLIANT! :D Thank you very much for this!

Wednesday, March 8, 2023 - 9:16:02 AM - Greg Robidoux Back To Top (90989)
Hi Shamas,

does this work if you use VARBINARY(max)?

Wednesday, March 8, 2023 - 1:29:09 AM - Shamas Back To Top (90987)
I use this code and I extract the Images but when trying to open any image or pdf, it is saying not correct format.

Only change ( I have data saved in nvarchar(max) column. I tried to convert and it fails, then I use cast and I worked).

Have any idea what can i do with this?

Thursday, September 1, 2022 - 1:20:18 PM - Pao Back To Top (90433)
Good afternoon. Thank you for your instructions. Using sql server 2017. The import works perfectly. When I run the export, it tells me that it runs successfully but does not export the file. I am sysadmin and enabled OLE. Could you tell me what is missing?


Use master
Go
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure


CREATE TABLE Pictures (
pictureName NVARCHAR(40) PRIMARY KEY NOT NULL
, picFileName NVARCHAR (100)
, PictureData VARBINARY (max)
)
GO
---------------

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

-------------------------

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


exec dbo.usp_ImportImage 'calendario1','E:\Pao\IMAGENES','calendario1.jpg'
go

select * from Pictures 'aqui se ve cargada
go

exec usp_ExportImage 'calendario1','C:\Users\Pao\Desktop\exportacion','calendario1.jpg'
go

Tuesday, July 12, 2022 - 7:58:03 PM - Andres L Back To Top (90250)
Muchas gracias, ha sido de mucha ayuda.

Monday, May 30, 2022 - 8:51:06 PM - Sonny Back To Top (90121)
Hello, thank you for this article. It is working very well for me and I was grateful to find it.

My question is, how would you recommend I go about reducing the file sizes of my exported pdf images?
My customer is complaining that the exported images are too large to ingest.

Tuesday, May 17, 2022 - 1:59:36 AM - Peter Back To Top (90091)
Hi Eli,
Thanks for the article.
How can I use this method to get all images in one folder in?

Saturday, April 2, 2022 - 4:58:11 AM - julian harrall Back To Top (89961)
Excellent piece of SQL - Thank you :)

Friday, August 27, 2021 - 10:04:13 AM - Woodie Back To Top (89178)
Brilliant, Thanks a lot!!

Wednesday, August 11, 2021 - 11:31:00 AM - Camila Back To Top (89115)
Thank you, your code helped me a lot.
Thanks from Brazil!!

Monday, June 14, 2021 - 9:05:33 AM - Rahim Kolahsaz Back To Top (88851)
Thank you
that was perfect

Monday, April 19, 2021 - 1:47:48 PM - Tim Kehoe Back To Top (88565)
You must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles, so you could also be added to one of those server roles.

Thursday, April 1, 2021 - 7:26:12 AM - Elaine Back To Top (88480)
I have permission limit for the pre-requisite, what can I do?

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 107
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 5
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 111
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 9
You do not have permission to run the RECONFIGURE statement.
Msg 15151, Level 16, State 1, Line 11
Cannot add the server principal 'Enter here the Login Name that will execute the Import', because it does not exist or you do not have permission.

Friday, March 5, 2021 - 1:11:19 PM - Mike Cooper Back To Top (88346)
Great post. Thanks. Love the simplicity of it too.

Wednesday, February 10, 2021 - 12:07:13 PM - Tim Kehoe Back To Top (88211)
An alternate method to export/import binary data into SQL Server

One method I like is using PowerShell. You will need to "Install-Module -Name SqlServer" if you have not already done so. I have been using the export script, which I call ExtractBinaryColumn for a while, but just recently created an import one. I have exported email attachments from SQL database mail, RDLs and RDSes from Reporting Services, session data from state databases, and documents from 3rd party databases. In the past I extracted documents from SharePoint when we had content databases in-house. (SharePoint in the cloud is a completely different animal.) The binary data can be Word documents (AdventureWorks2017 example in script), Excel spreadsheets, PDF files, or even executable files. I have tried to make the script easy to use in different situations using parameters. See the syntax comments at the top of the scripts for example parameter usage, including one for the MSSQLTips dragon example. Change the script for your specific usages, like updating data instead of inserting data. First read through the script, test them thoroughly, and use at your own risk.

## Extract of Sql Server Binary to file
# Examples:
# .\extractbinarycolumn.ps1 -limit 2
# .\extractbinarycolumn.ps1 -limit 2 -table ProductPhoto -SourceColumn LargePhoto -FileName LargePhotoFileName -Filter ''
# .\ExtractBinaryColumn.ps1 -limit 2999 -server SSRSServerName -database ReportServer -schema dbo -table Catalog -Sourcecolumn content -Filename Name -filetype ".rdl" -Filter "where type = 2" -OutputFolder "c:\windows\temp"
# .\extractbinarycolumn.ps1 -limit 1 -server SQLServerName -database msdb -schema DBO -table sysmail_attachments -SourceColumn attachment -FileName filename -filetype ".csv" -Filter "where attachment_id = 68"
# .\extractbinarycolumn.ps1 -database MyDatabase -table Pictures -schema "dbo" -SourceColumn PictureData -FileName picFileName -OutputFolder "C:\Windows\Temp\" -Filter ""

Param([string]$server = "(local)",
$database = "AdventureWorks2017",
$schema = "Production",
$table = "Document",
$SourceColumn = "Document",
$Filter = " WHERE FileExtension = '.doc'",
[string]$OutputFolder = "c:\windows\temp\", # Include trailing slash
[string]$FileName = "FileName",
[string]$filetype = "",
[int]$limit=5
)

$bufferSize = 8192; # Stream buffer size in bytes.
# Select-Statement
$Sql = "SELECT distinct TOP $limit cast($FileName As varchar(max)) + '$filetype'
, cast($SourceColumn as varbinary(max))
FROM $schema.$table with (nolock)"

if ($Filter -ne $null)
{
$Sql = $Sql + " " + $Filter
}

Write-output "
Here is the query create your input and the defaults:

"
$Sql
Write-output "

"
# Open ADO.NET Connection
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$Server;" +
"Integrated Security=True;" +
"Initial Catalog=$Database";
$con.Open();

# New Command and Reader
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;
$rd = $cmd.ExecuteReader();

# Create a byte array for the stream.
$out = [array]::CreateInstance('Byte', $bufferSize)

# Looping through records
While ($rd.Read())
{
Write-Output ("Exporting: {0}" -f $rd.GetString(0));
# New BinaryWriter
$fs = New-Object System.IO.FileStream ($OutputFolder + $rd.GetString(0)), Create, Write;
$bw = New-Object System.IO.BinaryWriter $fs;

$start = 0;
# Read first byte stream
$received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);
While ($received -gt 0)
{
$bw.Write($out, 0, $received);
$bw.Flush();
$start += $received;
# Read next byte stream
$received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);
}

$bw.Close();
$fs.Close();
}

# Closing & Disposing all objects
$fs.Dispose();
$rd.Close();
$cmd.Dispose();
$con.Close();

Write-Output ("Finished");


=======================================================================================================



## Import of Binary file to Sql Server
# Examples:
# .\importbinarytocolumn.ps1 -table ProductPhoto -DestinationColumn LargePhoto -FileName "no_image_available_large.gif"
# .\importbinarytocolumn.ps1 -limit 2 -table ProductPhoto -DestinationColumn LargePhoto -FileName "photo.bmp" -OtherColumnsToPopulate ",LargePhotoFileName" -OtherColumnValues ",'photo.bmp'"
# .\importbinarytocolumn.ps1 -database MyDatabase -table Pictures -schema "dbo" -DestinationColumn PictureData -FileName "Dragon.jpg" -OtherColumnsToPopulate ",pictureName,picFileName" -OtherColumnValues ",'DRAGON','Dragon.jpg'"

Param([string]$server = "(local)",
$database = "AdventureWorks2017",
$schema = "Production",
$table = "Document",
$DestinationColumn = "Document",
$OtherColumnsToPopulate="", # comma separated with initial comma
$OtherColumnValues="", # comma separated with initial comma and single quotes for literals
[string]$inputFolder = "c:\windows\temp\", # Include trailing slash
[string]$FileName = "FileName"
)

$bufferSize = 8192; # Stream buffer size in bytes.
# Open ADO.NET Connection
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$Server;" +
"Integrated Security=True;" +
"Initial Catalog=$Database";
$con.Open();

# Create a byte array for the stream.
$out = [array]::CreateInstance('Byte', $bufferSize)

Write-Output ("Importing: " + $inputFolder + $FileName )
# New BinaryReader
$fs = New-Object System.IO.FileStream ($inputFolder + $FileName), Open, Read
$br = New-Object System.IO.BinaryReader $fs;
[int]$filesize = $fs.Length
$binary = $br.ReadBytes($filesize)

$br.Close();
$fs.Close();

# Select-Statement
$Sql = "INSERT INTO $schema.$table ($DestinationColumn" + $OtherColumnsToPopulate + ") VALUES(@binvariable" + $OtherColumnValues + ")"

Write-output "
Here is the query created from your input and the defaults:

"
$Sql
Write-output "

"
# New Command and Writer
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;
$addparam = $cmd.Parameters.Add("@binvariable",$binary)
$wr = $cmd.ExecuteNonQuery();

#}

# Closing & Disposing all objects
$fs.Dispose();
$cmd.Dispose();
$con.Close();

Write-Output ("Finished");

Tuesday, January 12, 2021 - 2:03:35 AM - KP Back To Top (88031)
I have image type field and all data begins with '0x615C040514410100FE00BD'
How can I get the image file to store to a drive?

Thursday, December 17, 2020 - 7:58:51 AM - Brendan Keogh-Smith Back To Top (87926)
Very helpful, Thanks

Monday, November 2, 2020 - 9:35:54 AM - rsa Back To Top (87740)
saved my life, thank you so much.

Wednesday, May 20, 2020 - 3:15:21 PM - Jason Back To Top (85710)

It looks like it only picks up the first page of a multipage tiff file.  Do you know of a way to import them as well?


Friday, January 17, 2020 - 8:27:22 AM - Justin Rister Back To Top (83829)

Hi Eli, great article. I have an existing table in SQL with 500 records containing Image data. Is it possible to modify the SP to loop iterate through an entire table extracting all 500 images?

Thank you


Monday, December 30, 2019 - 4:32:40 PM - Arlen Back To Top (83561)

Good post and I love the simplicity; however, I am not seeing it export the file itself to any directory including those that allow for everyone to write. When the SPROC runs, it reads that it completes successfully, but no image exported. Is there another set of permissions that I am missing that need to be assigned to the app user?

Thanks.


Wednesday, November 27, 2019 - 5:15:59 AM - L Back To Top (83206)

Two notes:

Only works on 2012 and later, and

Bulk will only references drives from the server, so the 'C:' in the example will be the 'C:' drive of the server.

PS Very good!


Thursday, October 24, 2019 - 11:49:16 AM - Brian Alm Back To Top (82887)

This was a a great alternative to the bulk copy method. I used your code to help me add it to a cursor so I could dynamically name each file as it looped through. Here is my code.

BEGIN
   DECLARE  @PicName NVARCHAR (100)
   DECLARE @ImageFolderPath NVARCHAR(1000)
   DECLARE @Filename NVARCHAR(1000)
   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT
   DECLARE @ID VARCHAR(40)

  DECLARE CurPhoto CURSOR FAST_FORWARD FOR
  SELECT counter,   -- select counter to match ID of photo
       CAST(date as varchar) +'_' + employeeid + '_' + name + '_'+ punchtype  -- creates a photo name
FROM   Image
OPEN CurPhoto

FETCH NEXT FROM CurPhoto INTO @ID,  @PicName

SET @ImageFolderPath = 'C:\Datamaxx\images'  -- file path where photos are saved

WHILE @@FETCH_STATUS = 0
BEGIN

   SET NOCOUNT ON

   SELECT @ImageData = ( 
         SELECT convert (VARBINARY (max), image, 1)  -- converts image to binary data
         FROM IMAGE
         WHERE Counter = @ID
         );
 SET @Filename = @PicName + '.jpg'  --creates the file name to be saved

   SET @Path2OutFile = CONCAT (  -- creates file path and file name to be exported
         @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

  FETCH NEXT FROM CurPhoto INTO @ID,  @PicName
END

CLOSE CurPhoto
DEALLOCATE CurPhoto
END;

SET NOCOUNT OFF

Wednesday, October 16, 2019 - 11:07:55 AM - Eli Leiba Back To Top (82801)

Please enable the 'Ole Automation Procedures' option by using the following script:

USE master; 
GO 
EXEC sp_configure 'show advanced option', '1'; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
EXEC sp_configure 'Ole Automation Procedures', '1'; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO

Wednesday, October 16, 2019 - 4:59:59 AM - Martin Back To Top (82793)

There's just one problem: on some servers you might get an error:

SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.


Wednesday, April 3, 2019 - 1:56:44 PM - Alex Back To Top (79457)

Thanks for posting this tutorial! I'm glad I finaly learned a simple way to extract images from SQL server today :)

Could you please explaine why there is a need to convert PictureData to VARBINARY (max inside select @ImageData? I thought when you create the Picture table the PictureData is already set to varbinary.

Thanks.


Thursday, January 10, 2019 - 12:11:35 PM - Robert Bardwell Back To Top (78700)

Yes yes yes!!! Worked like a charm and saved hours of time. 

As some asked below, yes it's possible to extract all the images from the table into a single folder...  I just moved the variables directly into the code instead of running it as a stored procedure, making the filename change dynamically based on the image description in my table... and then did a WHILE loop to pull them all out.


Wednesday, November 14, 2018 - 1:42:26 PM - Ali Back To Top (78247)

Hi 

Thank you for your good article.

I want to import excel file which contain picture, do you have a idea how can I do that.

Many Thanks,

Ali


Tuesday, April 17, 2018 - 4:52:35 PM - Carlos Torres Back To Top (75724)

Hi Eli,

I did all those steps in a SQL Server 2008

I dont get any error message but when I checked the folder , its empty. Any idea why ?

Here is my code:

*******************************************

 

--Drop table [BLOBtest]

---------------------------------------

--CREATE TABLE [BLOBtest](

-- [Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

-- [Extension] [varchar](50) NULL,

-- [FileName] [varchar](200) NULL,

-- [Doc_Content] [varbinary](max) NULL

--) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

--INSERT [dbo].[BLOBtest] ([Extension] ,[FileName] , [Doc_Content] )

--SELECT 'jpg', 'mmsync.jpg',[Doc_Data].*

--FROM OPENROWSET 

--    (BULK '\\Testfolder\TEMP\images\mmsync.jpg', SINGLE_BLOB)  [Doc_Data]

    

--INSERT [dbo].[BLOBtest] ([Extension] ,[FileName] , [Doc_Content] )

--SELECT 'jpg', 'BC image 1.jpg',[Doc_Data].*

--FROM OPENROWSET 

--    (BULK '\\Testfolder\TEMP\images\BC image 1.jpg', SINGLE_BLOB)  [Doc_Data]

 

--INSERT [dbo].[BLOBtest] ([Extension] ,[FileName] , [Doc_Content] )

--SELECT 'jpg', 'Russia2018.jpg',[Doc_Data].*

--FROM OPENROWSET 

--    (BULK '\\Testfolder\TEMP\images\Russia2018.jpg', SINGLE_BLOB)  [Doc_Data]

    

Select * from [BLOBtest]

---------------------------------------

 

 

DECLARE @outPutPath varchar(50) = '\\Testfolder\TEMP\images\BLOB'

, @i bigint

, @init int

, @data varbinary(max) 

, @fPath varchar(max)  

, @folderPath  varchar(max) 

 

--Get Data into temp Table variable so that we can iterate over it 

DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )

 

INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])

Select [Doc_Num] , [FileName],[Doc_Content] FROM  [dbo].[BLOBtest]

 

--SELECT * FROM @table

 

SELECT @i = COUNT(1) FROM @Doctable

 

WHILE @i >= 1

BEGIN 

 

SELECT 

@data = [Doc_Content],

@fPath = @outPutPath + '\'+ [FileName],

@folderPath = @outPutPath + '\'+ [Doc_Num]

FROM @Doctable WHERE id = @i

 

  -- EXEC master.master.dbo.xp_cmdshell @folderPath

  -- Create folder first

  -- EXEC master.dbo.xp_cmdshell @outPutPath

  -- EXEC  [dbo].[CreateFolder]  @folderPath

 

  Begin Try

  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created

  EXEC sp_OASetProperty @init, 'Type', 1;  

  EXEC sp_OAMethod @init, 'Open'; -- Calling a method

  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method

  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method

  EXEC sp_OAMethod @init, 'Close'; -- Calling a method

  EXEC sp_OADestroy @init; -- Closed the resources

  End Try

  

  Begin CATCH

  EXEC sp_OADestroy @init; -- Closed the resources

  End CATCH

 

  print 'Document Generated at - '+  @fPath   

 

  --Reset the variables for next use

  SELECT @data = NULL  

  , @init = NULL

  , @fPath = NULL  

  , @folderPath = NULL

  SET @i -= 1

 

END

 


Thursday, March 15, 2018 - 10:15:44 AM - Kaare Back To Top (75427)

 

 Is it possible to extract multiple pictures at once with the same Exec?


Sunday, August 6, 2017 - 7:24:32 PM - Nosa Osayamwen Back To Top (64189)

 

 

Thanks for a very nice article. I tried a scenario whereby I want to update a picture for an employee with a new picture, I could not get it to work, can you help me out? Thanks.


Wednesday, July 19, 2017 - 7:46:55 AM - Eli Leiba Back To Top (59662)

 The error handling TRY & Catch block was added to the export procedure.

 


Wednesday, July 19, 2017 - 1:27:01 AM - Salman Sadiq Back To Top (59640)

Awesome tsql code to handle and store images files in db

a lil costly on memory if something strangled due to any error as said by scott coleman.

Overall worth teaching.

Thanks for sharing.


Monday, July 17, 2017 - 6:49:49 PM - jeff_yao Back To Top (59502)

My concern is that sp_OA* system stored procedures require sysadmin privileges as stated here - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oacreate-transact-sql.

Further, using sp_OA* system stored procedures is out-dated and a potential issue for a company that subject to 3rd party audits.


Monday, July 17, 2017 - 1:40:33 PM - Scott Eichman Back To Top (59486)

 Interesting solution for reading and writig files. MS-SQL has never been very good about natively interfacing with the OS. The way I solved the problem of reading and writing files was to create CLR C# functions. One to read a file into a VARBINARY(MAX) and the other to write a VARBINARY(MAX) to a file. Using C# makes the actions very efficient.

Thanks for the article!

Scott


Monday, July 17, 2017 - 11:01:42 AM - David Primus, DBA Back To Top (59479)

sp_OACreate method is notorious for memory leaks.  Best Practices is to run these from staging sql servers, not on production sql servers. SqlCLR  proovides "managed code" which largely replaces these older unmanaged coding techniques.

 


Monday, July 17, 2017 - 9:50:59 AM - Scott Coleman Back To Top (59474)

This is a good post for anyone who did not know T-SQL alone could be used to read and write files.  However I have a complaint.

The file writing subroutine is seriously flawed because it uses object automation with no error handling.  The possible errors that could occur include a nonexistent or malformed path, permission errors, disk full errors.  Any of these would interrupt the routine and skip the call to sp_OADestroy, leaving the stream object allocated in memory forever (or until the next reboot, whichever comes first).  I would not object so strenuously if you had included a note such as "Error handling removed for clarity", but to not even mention it is unacceptable.

I think moving the file IO to CLR routines is a better plan.  Using .Net managed code instead of COM objects should lead to a more stable SQL environment.

Some CLR file IO tips on this website:

https://www.mssqltips.com/sqlservertip/2341/use-the-sql-server-clr-to-read-and-write-text-files/
https://www.mssqltips.com/sqlservertip/2302/extending-file-system-operations-in-sql-server-using-clr/


Monday, July 17, 2017 - 2:14:30 AM - György Görög Back To Top (59448)

This is a nice post. It may be worth to mention that many image formats that are designed to go through the internet w/o further encoding, that is, contain only ASCII characters < 128, can be stored in text columns, not even nText. GIF and JPG come to mind. My apps store these images this way for decades now (I have to admit) and I never had any problems with that. An advantage is that you can read the first few characters that specify the format w/o getting the whole image, in case you don't store the format in a separate column.

But true, varbinary is really insensitive for format and compression etc. so it's more universal.   

 

 















get free sql tips
agree to terms