Export Documents saved as Blob / Binary from SQL Server

I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.

To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .

CREATE TABLE [dbo].[Document](
	[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]

How to Insert Blob into Database

For demo purpose, we will save two files in our table using below queries

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB)  [Doc_Data]

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'html', 'Progress.html',[Doc_Data].*
FROM OPENROWSET 
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB)  [Doc_Data]

If we try to see content in actual table, it will look like

Insert Blob into Database
Insert Blob into Database

Export Blob From SQL Server and save it as a file

For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.

Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.

USE [POC]
DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract 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].[Document]
 
--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable
 
WHILE @i >= 1
BEGIN 

	SELECT 
	 @data = [Doc_Content],
	 @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
	 @folderPath = @outPutPath + '\'+ [Doc_Num]
	FROM @Doctable WHERE id = @i
 
  --Create folder first
  EXEC  [dbo].[CreateFolder]  @folderPath
  
  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
 
  print 'Document Generated at - '+  @fPath   

--Reset the variables for next use
SELECT @data = NULL  
, @init = NULL
, @fPath = NULL  
, @folderPath = NULL
SET @i -= 1
END

Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.

Below image shows output in action :

SQL Server Export Blob as File
SQL Server Export Blob as File

Posted

in

by

Tags:


Related Posts

Comments

55 responses to “Export Documents saved as Blob / Binary from SQL Server”

  1. asdf Avatar
    asdf

    Hi,
    I am trying to use your code to export varbinary from the database to file, but I am not seeing any folders being created or files been exported. No errors.
    So, will the files get created on the database server or on the machine on which we run this T-sql ?.
    Thanks for your help.

    1. Jitendra Zaa Avatar

      It would be created in local machine. In above code, line 2, you need to make sure that path exists already. Any sub folder needed would be handled by above code

      1. asdf Avatar
        asdf

        Thanks for the response. Yes, the path does exists. And now when I uncommented the raiserror stmt from the [dbo].[CreateFolder] procedure ( I used your code for this too), I am getting below error-

        Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 36

        On printing values for @init ,OLEfolder as seen in the below code –

        — check if folder exists

        EXEC @init=sp_OAMethod @OLEfilesytemobject, ‘FolderExists’, @OLEfolder OUT, @newfolder
        print @OLEfolder
        print @init

        —-Results—-
        0
        0

        Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 36
        This tell me, that the sp_OAMethod is not able to find the folder .
        Please advise. Thanks again !

        1. asdf Avatar
          asdf

          Oh found out that the files are being exported on the database server.
          Thanks again for posting this code.

          1. V** Avatar
            V**

            Hi, I am experiencing the same problem. How did you manage to solve it?

      2. Jack Avatar
        Jack

        Hi,

        I tried to get this working and everything seems to process fine without no errors, I get Documents generated at – but when i navigate to the folder; the folder structure has been created e.g. there is 4 attachments so folder 1,2,3,4 have been created but there is nothing in the folders.

        Please help!!!!

      3. kamal.ntmca@gmail.com Avatar
        kamal.ntmca@gmail.com

        Document Generated message is diplayed but the documents are not created.

        1. krishna kurtakoti Avatar
          krishna kurtakoti

          For Example,if you want to documents to be written in Newfolder(2),right click folder > Properties > Security > Edit > Add > Advanced > FindNow.

          C:\Users\krishna\Desktop\Newfolder(2)

          In the search results,
          select
          SQLSERVERMSSQLUser$sqlServerName$MSSQLSERVER.

          Here,sqlServerName is the name of your server.Click O.k.
          Under Permissions,tick Full Control > Apply > O.k.

          Now you will be able to download the files.

      4. xaliloff08281993gmailcomAnvar Avatar

        Hi Jitendra,

        Thanks for great article.

        I am receiving the following error in there:
        Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 31

        Can you suggest what can be the reason of it and how to fix it please?

        Thanks

  2. Ferenc de Tyssonsk Avatar
    Ferenc de Tyssonsk

    Thank you Jitendra, exactly what I was looking for.
    Just applied it to create a demo about “Compressed” data using the Compress functionality.
    Saving a columns value as a file (extension = .zip). 7-zip flawlessly extracts the data within to it’s original uncompressed value.

    1. Sathya Avatar
      Sathya

      Hi Tysson, Can you please email me the code which you have done for compressed data..? wishsathya@gmail.com

  3. Lipsita Avatar
    Lipsita

    Hi Jitendra,

    For me above query got executed and stored the attachments in my given path also but attachment size is showing 0 Bytes

    1. JP Avatar
      JP

      I am getting same, could you managed to sort this issue ? If please advise.

  4. Chris Voorhees Avatar
    Chris Voorhees

    Good Heavens – this is truly great. I really appreciate google (and obviously this kind of results and your efforts). THANK YOU!

  5. cedricleuchter Avatar

    How is it possible to create folders and export the files to a shared network drive instead of the local machine? thanks in advance!

  6. Jack Avatar
    Jack

    Hi,

    I can run the scripts successfully but when i go into the folders the scripts have created there is nothing in the folders. The contents are not being exported.

    Please help!

  7. Jack Avatar
    Jack

    Hi,

    I can run the script successful and it creates the folder structures but it does not have any contents in the folder. I am using a local SQL database for testing purposes.

    Please help!

    1. Алексей Гаврилов Avatar
      Алексей Гаврилов

      Just add WAITFOR DELAY ’00:00:01′; after folder creation

  8. Shardul Pathak Avatar
    Shardul Pathak

    Thank you Jitendra, it really helped me creating files from BLOB data.

    I am able to create file on specified path but the I am not able to open the file. Below is the message appears when double click on the file,

    We’re sorry. We can’t open “File_Name” because we found a problem with its contents.

    Details,
    The file is corrupt and cannot be opened.

    Thanks,
    Shardul Pathak

    1. jack Avatar
      jack

      You probably need to name the file as .jpg?

  9. Shardul Pathak Avatar
    Shardul Pathak

    Hi Jitendra,

    I am creating file from BLOB data using SQL, file created successfully but not able to open the file. Below is the message appears when trying to open the file,

    We’re sorry. We can’t open “file_name” because we found a problem with its contents.

    Details, The file is corrupt and cannot be opened.

    1. Pavithrayani K Avatar
      Pavithrayani K

      I also face similar issue for few of the documents, Please share if you have already found a resolution

  10. locttrang06 Avatar

    It’s seem work with role system admin. If I just want to export by normal SQL admin. How can I do that?
    Because I don’t want to grant full permission for some guys want to export file.

  11. gbr405harath Avatar

    Hi Sir, How can we use unc path instead of local , Please help me

  12. locttrang06 Avatar

    Hi,
    I am trying to use your code to export varbinary from the database to file, but I am not seeing the content.
    Do you have any idea?

    Error Failed to load PDF Document.

    Thanks for your help.

  13. lemming3k Avatar
    lemming3k

    Looks like I’m having a similar problem to others – SQL shows ‘document generated at….’
    But no documents show in the location specified.
    I suspect it’s permissions related, though I’m surprised it doesn’t through any errors.

  14. Trey Avatar
    Trey

    Hi, im having a hard time here. I am following your example here but for some reason it refuses to work despite many efforts. Whenever I run the code it receive an error message that says nothing but “Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 30 [Batch Start Line 0]” for every row. Please help!

  15. Peter Edd Avatar

    This worked great, thanks so much for posting. I had to create the directory local to where my MSSQL server instance was running.

  16. qwerty Avatar
    qwerty

    While inserting into temp doc_table, Im getting the error like,

    “Msg 8152, Level 16, State 2, Line 13
    String or binary data would be truncated.
    The statement has been terminated.”

    Any possible solutions please?

  17. Bhuvana Ganesh Avatar

    Thanks Jitendra 🙂

  18. Gabriel Ferraz Avatar

    Jitendra, thanks for sharing this! It is awesome how fast the code runs! Well done

  19. Patrick Verseput Avatar
    Patrick Verseput

    Thank you very much. It’s code was a lifesaver.

  20. Theo Geerman Avatar
    Theo Geerman

    Hi Dear,
    Thank you for the great info.
    Is there away to create the files from Blob in to a single directory? meaning not one folder for each file but all in the same directory?
    Thank you very much for your great knwoledge

    Regards,
    Theo

  21. Charles Avatar
    Charles

    This actually works great in my environment and writes the files locally to the database server. The only issue I’m having is in one environment, the pdf’s are being created all the same size, and are therefore showing corrupted and will not open. The error says “Adobe Acrobat Reader could not open ‘filename.pdf’ because it is either not a supported file type or because the file has been damaged (for example it was sent as an email attachment and wasn’t correctly decoded).
    I suspect it is related to permissions but haven’t narrowed it down yet…any suggestions are appreciated.

  22. Raga Avatar
    Raga

    This works perfectly. Thank you for the expertise on this subject.

    I would further like to know how this transact-SQL can be converted to a stored procedure with an input of Doc_Num passed as a parameter from a windows form. I’d like a user to select a document from a list (gridview etc.) and convert and display on a PDF viewer.

    1. Michael Avatar

      create procedure sp_export_document(@Doc_Num int)
      as
      begin
      set nocount on
      –exec sp_export_document 9

      DECLARE @outPutPath varchar(50) = ‘C:\temp\Export’
      , @i bigint
      , @init int
      , @data varbinary(max)
      , @fPath varchar(max)
      , @folderPath varchar(max)

      SELECT
      @data = [Doc_Content],
      –@fPath = @outPutPath + ‘\’+ format([Doc_Num],’000000000’) + ‘ – ‘ +[FileName],
      @fPath = @outPutPath + ‘\’+[FileName],
      @folderPath = @outPutPath
      from [dbo].[X_Document] Where Doc_Num = @Doc_Num

      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

      select ‘Document Generated at – ‘+ @fPath

      end

  23. xaliloff08281993gmailcomAnvar Avatar

    Great article!!!

    Thanks, it worked

  24. Betsy Duren Avatar
    Betsy Duren

    Thank you, this is exactly what I needed! Worked like a charm!

  25. ETL Avatar
    ETL

    Thank you for such amazing detail on the aspect i am just having one trouble how can i export there files to shared file or folder path ?

  26. Ravi Kant Avatar
    Ravi Kant

    Hi All, I have used these scripts and it is running successfully and I used these steps to run the script :-

    First Run below these two scripts from the url :- https://www.jitendrazaa.com/blog/sql/sqlserver/sql-server-create-folders-using-t-sql-ole-automation/

    1. T-SQL to enable Ole Automation Procedures in SQL Server
    2. Next step is to create a stored procedure……

    then run the above three scripts.

    Note :- change the input and output path accordingly

    Regards,
    R.K

  27. Ravi Kant Avatar
    Ravi Kant

    Thank you Jitendra for sharing this. It is awesome! Well done 🙂

  28. Tom Avatar
    Tom

    Thank you for this. I am able to generate documents after following your steps. How do i hide specific documents that starts with the name ‘2015’ from being generated?

    Thanks

  29. sumit sharda Avatar
    sumit sharda

    I also want to include datemodified from my table

  30. secordwu Avatar
    secordwu

    Great. This is exactly what I need. Thank you very much

  31. Prasanna Avatar
    Prasanna

    I am using AWS SQL RDS to store the BLOB data. I want to convert the BLOB data to file and store on my local machine. Is this script will work for it? Is there any SP available for the same?

  32. Vince Avatar
    Vince

    Hi, can this work for Sybase?
    We can’t seem to identify where the filename or filetype is stored.

  33. Marketing DIY (@marketingdiy) Avatar

    Should this also work for extracting documents from a Sybase database?
    I can’t seem to see where the file type and name are stored, the obvious ones are empty, but plenty od files on the Blob field

  34. Vani K Avatar

    Hi,
    The PDF’s are getting created wonderfully. But the PDF file itself is corrupted and doesn’t open. PDF reader throws an error saying this file cannot be opened, and may be corrupt.
    Is there a way to fix this issue and have PDF’s actually reflect the blob correctly?
    Please help.

  35. Jalen Avatar
    Jalen

    I just wanted to comment that, WOW this actually worked. For several hours I’ve been looking for a method to extract several hundred pdfs from our DB and your solution worked like a charm. Many thanks!

  36. Deepak Avatar
    Deepak

    I am getting below error when inserting data in temp table. Any solution for this

    invalid length parameter passed to the right function

  37. Yi Zhou Avatar
    Yi Zhou

    This works great! But however I didn’t get all docs. Some of documents didn’t get saved. Why is that? How to find where went wrong?

  38. David Plume Avatar
    David Plume

    Very nice and as others have said, amazingly fast — 995 files, 330 MB in seconds. Wow. If you are on a SQL server (say a local server of SQLExpress) you can enable the system methods using the following query statements:

    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘Ole Automation Procedures’, 1;
    GO
    RECONFIGURE;
    GO

    Thanks!
    David

  39. Murali Krishna Avatar
    Murali Krishna

    Hi I want to create multiple subfolders inside main folder . Please let me know how we can achieve this.

  40. mark Avatar

    Worked great the first time! Thanks for the great article!

  41. Ziad Fawzi Avatar
    Ziad Fawzi

    Thank you so much.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading