Friday, October 16, 2009

Exporting Binary Files To The File System

In my last post I demonstrated how to use SSIS to load binary files into a SQL Server 2005 VARBINARY(MAX) column, http://jahaines.blogspot.com/2009/10/ssis-importing-binary-files-into.html.  This post will focus on recreating the binary documents on the file system.  I will be using a combination of TSQL and the BCP utility to perform the export, http://msdn.microsoft.com/en-us/library/ms162802.aspx.  I will be using the same table and data from the last post.  I will start by creating the TSQL to dynamically create a BCP command.

Below is the stored procedure I will use to export the data.  You will see that I have decided to use a cursor to process all documents.  The procedure also accepts a DocID, which limits the export to a single document.  A cursor is fine here because we are limited to executing a single BCP command; however, you could create an SSIS package that executes the stored procedure across multiple streams, if you need parallel processing.

CREATE PROCEDURE usp_ExportBinaryFiles(
    @DocID INT = NULL,
    @OutputFilePath VARCHAR(500) = 'C:\'
)
AS 
BEGIN
 
DECLARE @sql VARCHAR(8000)
 
IF @DocID IS NULL --Open Cursor to export all images
BEGIN
 
    DECLARE curExportBinaryDocs CURSOR FAST_FORWARD FOR
    SELECT 'BCP "SELECT Doc FROM [tempdb].[dbo].[Documents] WHERE DocId =' 
        + CAST(DocId AS VARCHAR(5)) + '" queryout ' + @OutputFilePath 
        + DocName + '.' + DocType + ' -S A70195\Dev -T -fC:\Documents\Documents.fmt'
    FROM dbo.Documents
 
    OPEN curExportBinaryDocs
    FETCH NEXT FROM curExportBinaryDocs INTO @sql
 
    WHILE @@FETCH_STATUS = 0
        BEGIN
            --PRINT @sql
            EXEC xp_cmdshell @sql,NO_OUTPUT
            
            FETCH NEXT FROM curExportBinaryDocs INTO @sql
        END
 
    CLOSE curExportBinaryDocs
    DEALLOCATE curExportBinaryDocs
END
ELSE --Export a single image
BEGIN
    SELECT @sql = 'BCP "SELECT Doc FROM [tempdb].[dbo].[Documents] WHERE DocId =' 
        + CAST(DocId AS VARCHAR(5)) + '" queryout ' + @OutputFilePath 
        + DocName + '.' + DocType + ' -S A70195\Dev -T -fC:\Documents\Documents.fmt'
    FROM dbo.Documents
    WHERE DocID = @DocID
    
    --PRINT @sql
    EXEC xp_cmdshell @sql,NO_OUTPUT
END
 
END
GO

The above stored procedure dynamically builds a BCP command.  Currently the stored procedure has two main parameters, DocID and OutputFilePath.  DocID is used when you want to export a single row, if no value is supplied the stored procedure will export all documents.  The outputFilePath is the directory where the files will be exported.  You will note that I have hard coded my server name and format file path.  You can add additional parameters if you need these attributes to be dynamic.  The next item on my list is to actually create the format file.  Create a .fmt file somewhere on your file system.   I put mine in the same folder as my other documents.  Copy the code below into the format file.

9.0   
1   
1       SQLBINARY     0       0       ""   1     Doc                                       ""  

You may be asking yourself, why the format file looks skimpy, or is lacking content.  The format file is lacking content because it only has what we need.  All we need in the format file is the VARBINARY(MAX) column and its data type. You can find more info on format files here, http://msdn.microsoft.com/en-us/library/ms191516.aspx.

Once the format file is in place, we can execute our stored procedure to process all documents.

EXEC dbo.usp_ExportBinaryFiles @OutputFilePath = 'C:\Documents\BCP_Out\'

That’s it!  It is that easy to output VARBINARY data onto the file system.  SQL Server 2005 has a slue of tools that make working with binary data very simplistic.  I hope that you have learned something new. Please stay tuned, as I plan to focus more on TSQL concepts and performance considerations.

Until next time happy coding.

16 comments:

Ry-O said...

Hi,Adams.

I am a Japanese engineer.
I was very troubled.

I'm very lucky that I met your blog.
Thanks a lot!!
I'll try the way.

Unknown said...

I have also heard about the way of backup outlook 2003 accounts

Anonymous said...

It's "slew", not "slue".

Unknown said...

Great post , saved my day.
Thanks.

smurph said...

Excellent article! I was able to use this method to pull files out of a database for a 3rd party's software. This will save both my time and our users' times. Thanks a lot!

Anonymous said...

Very nice, saved my day too!!!!

Thanks

Anonymous said...

I was not able to open the pdf using adobe file (says not supported file type) that I dump on disk. What could be the reason?

Rajesh said...

Article is simply the best, thanks to the authors.

But when I'm trying to create this stored procedure I'm getting this error/message: Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'xp_cmdshell'. The stored procedure will still be created.

I'm using SQL Server Management Studio..
If someone knows a work around please let us know.

Thanks,
Rajesh

Adam Haines said...

Is xp_cmdshell enabled on the server? also try printing the command to make sure it will execute in a command line window.

Rajesh said...

Adam appreciate for replying back, however I passed through that step, I've modified my script as master.dbo.xp_cmdshell.

But I need your help in my situation..
I'm using SQL S M Studio to connect to databases on the servers, so my question is when I execute this can I extract the documents to my local?

Regards,
Rajesh

Rajesh said...

I mean to say databases on the network servers.*

Thanks,
Rajesh

Adam Haines said...

Unfortunately not. You will have to copy to the local server or a network share that the SQL Server service account has permission to. You can then copy the file to your desktop, from the share.

Another option would be to RDP to the server (mapping your local drive in the RDP session) and then you can copy the file to that drive once the command is executed.

Unknown said...

Hello

I try your code on my sql server table, but all the documents extract cannot be read, PDF,XLS,JPG and DOC.
Could you tell me why ?

Best regards
Luis Santos

Unknown said...

Exporting to PDF is not working.
HEELP

Nirav said...

Hello, How does format file will look if I run it against sql 2014?

Anonymous said...

Great post , saved my day.
Thanks a lot :)