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.

Stored Procedure Set Options.. A potential gotcha

I have seen a lot of problems with stored procedures not working correctly, all of a sudden.  There can be numerous reasons why the stored procedure is not working correctly, but one of  the most overlooked culprits is SET options.  I wanted to talk about how SET options can affect query results and behavior. The behavior in question applies to two SET options and is fully documented in BOL, http://msdn.microsoft.com/en-us/library/ms187926.aspx. The two SET options are ANSI_NULLS and QUOTED_IDENTIFIER.  When a stored procedure is created, the set options that are enabled for that session, are stored in metadata.  For each subsequent run, the metadata settings are used and all explicit set options, outside of the stored procedure, are ignored. In SQL Server 2005 and greater, you can use the catalog view, sys.sql_modules, to view properties of a given object.  If you find yourself in a situation where you need to override the metadata, you should drop and recreate your stored procedure with the correct SET options. Here is a sample query to check the metadata of a given object.

SELECT *

FROM sys.sql_modules 

WHERE OBJECT_ID = OBJECT_ID('usp_TestProc')

Now that we know our objective, let’s get to the code.

We will start by creating a simple stored procedure that creates a table variable and selects some data.  We will turn the ANSI_NULLS OFF prior to the stored procedure creation. 

USE [tempdb]

GO

--drop the sp and recreate with the correct set options.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TestProc]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_TestProc]

GO

SET ANSI_NULLS OFF --<------------ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE usp_TestProc

AS

BEGIN

DECLARE @t TABLE(

id INT,

col CHAR(1)

)

INSERT INTO @t VALUES (1,'a');

INSERT INTO @t VALUES (2,NULL);

SELECT *

FROM @t 

WHERE col = NULL

END

GO

Now that we have our stored procedure. , let’s check the metadata in sys.sql_modules, by running the code below.

SELECT *

FROM sys.sql_modules 

WHERE OBJECT_ID = OBJECT_ID('usp_TestProc')

You should something like the screenshot below:

Now we are going to execute the stored procedure, making sure to explicitly make the SET options different than the stored procedure. Remember ANSI_NULLS are turned OFF, in the stored procedure; however, we are going to set it to ON.  We do not expect to see results because we are turning the option on and NULL should evaluate to unknown.

SET ANSI_NULLS ON

GO

EXEC [usp_TestProc]

GO

Results: The stored procedure returned results even though we turned on ANSI_NULLS.  This query should not have returned anything because using = or <> to a NULL should resolve as UNKNOWN;  however, the stored procedure metadata overrode our SET option and thus we get a result.

 

Lets switch this around by dropping the procedure and recreating it with ANSI_NULL on and then explicitly turn it off.

--drop the sp and recreate with the correct set options.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TestProc]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_TestProc]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE usp_TestProc

AS

BEGIN

DECLARE @t TABLE(

id INT,

col CHAR(1)

)

INSERT INTO @t VALUES (1,'a');

INSERT INTO @t VALUES (2,NULL);

SELECT *

FROM @t 

WHERE col = NULL

END

GO

Now let’s execute the stored procedure, with the new SET options.

SET ANSI_NULLS OFF;

GO

EXEC [usp_TestProc]

GO

Results:  There were no results because ANSI_NULLS is turned on, which means you cannot use = or <> when comparing to NULL, regardless that we turned it OFF explicitly.

As you can see, even though we explicitly set ANSI_NULLS the option is ignored because the stored procedure’s metadata overrides all. This can make debugging a stored procedure a little more difficult, but it is a very good fact to know.  You can use a set option with in the stored procedure itself, which should override any global settings, for the duration of the stored procedure.  I have seen this problem bite people on the $%^ a few too many times and hopefully by reading this you will become more aware of the potential pitfalls, regarding SET options.