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

17 Comments

1. 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?

2. This is freaking BRILLIANT! :D Thank you very much for this!

3. Hi Shamas,

   does this work if you use VARBINARY(max)?

4. 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?

5. 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

6. Muchas gracias, ha sido de mucha ayuda.

7. 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.

8. Hi Eli,
   Thanks for the article.
   How can I use this method to get all images in one folder in?

9. Excellent piece of SQL – Thank you :)

10. Brilliant, Thanks a lot!!

11. Thank you, your code helped me a lot.
    Thanks from Brazil!!

12. Thank you
    that was perfect

13. 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.

14. 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.

15. Great post. Thanks. Love the simplicity of it too.

16. 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”);

17. I have image type field and all data begins with ‘0x615C040514410100FE00BD’
    How can I get the image file to store to a drive?