serving the solutions day and night

Pages

Tuesday, January 10, 2012

SQL Server - BLOB Import and Export

SQL scripts to insert File to BLOB field and export BLOB to File
  1. Using SQL server 2008, you can saved images / files to BLOB binaries and retrieve back them to file system.
  2. Import
    Test table structure:
    CREATE TABLE [dbo].[TestBlob](
    [tbId] [int] IDENTITY(1,1) NOT NULL,
    [tbName] [varchar](50) NULL,
    [tbDesc] [varchar](100) NULL,
    [tbBin] [varbinary](max) NULL
    ) ON [PRIMARY]
  3. Insert file to BLOB test table is fairly easy. Open Microsoft SQL server management studio, run the below script, script is inserting one pdf, doc, image and exe fil.
    Insert TestBlob(tbName, tbDesc, tbBin) Select '81.pdf','PDF file', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\81.pdf', Single_Blob) as tb

    Insert TestBlob(tbName, tbDesc, tbBin) Select 'mountain.jpg','Image jpeg', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\mountain.jpg', Single_Blob) as tb

    Insert TestBlob(tbName, tbDesc, tbBin) Select 'Questionnaire.docx','Doc Question', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\Questionnaire.docx', Single_Blob) as tb

    Insert TestBlob(tbName, tbDesc, tbBin) Select 'txpeng542.exe','Texpad Exe', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\txpeng542.exe', Single_Blob) as tb
  4. Export
    To export BLOB field to file, you need a SQL server utility called “bcp”, more info http://msdn.microsoft.com/en-us/library/ms162802.aspx. You can run bcp.exe from DOS command line or within SQL server management studio query window.
  5. Enable xp_cmdshell
    xp_cmdshell is a TSQL command to execute OS shell command, by default it’s not enabled. Here are the scripts to enable it 2 ways:

    1. EXEC sp_configure 'show advanced options', 1;
      GO
      RECONFIGURE;
      GO
      EXEC sp_configure 'xp_cmdshell',1
      GO
      RECONFIGURE
      GO
      If you get error, use 2nd option:
      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
      Msg 5808, Level 16, State 1, Line 1
      Ad hoc update to system catalogs is not supported.
      Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
      The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
      Msg 5808, Level 16, State 1, Line 1
      Ad hoc update to system catalogs is not supported.
    2. Use Surface Area Configuration(SAC) tool to enable
      MS has removed the SAC tool that was shipped in SQL Server 2005. The Services and Connections that were once managed in SAC tool should now be managed using the SQL Server Configuration Manager. So how do you manage the Database Engine features? You can now manage the SAC for the Database Engine using Policy Based Management. A new SAC Facet in SQL Server Management Studio exposes the properties necessary to make required changes.
      First, right-click on the instance name that you would like to configure in SQL Server Management Studio and select "Facets" from the context menu as shown below.
      Select SAC from the Facet list, set True to XPCmdShellEnabled properties.
  6. Creating a Format File, more info from microsoft link1, link2.
    Declare @sql varchar(500)
    SET @sql = 'bcp AdventureWorks2008R2.dbo.TestBlob format nul -T -n -f C:\dns\testblob.fmt -S ' + @@SERVERNAME
    select @sql
    EXEC master.dbo.xp_CmdShell @sql
  7. Open the format testblob.fmt file, this FORMAT file mapped the whole testblob table fields.
    10.0
    4
    1       SQLINT              0       4       ""   1     tbId             ""
    2       SQLCHAR             2       50      ""   2     tbName           Latin1_General_100_CS_AS
    3       SQLCHAR             2       100     ""   3     tbDesc           Latin1_General_100_CS_AS
    4       SQLBINARY           8       0       ""   4     tbBin            ""
    For retrieve image blob, need only the "tbBin" binary field. so manually modify the above format file to like below, Please note: original 8 change to 0 and save it:
    10.0
    1
    1       SQLBINARY           0       0       ""   1     tbBin            ""
  8. Execute the below script to export, 4 exported (pdf, doc, image and exe) files in the ddoc folder.
    Declare @sql varchar(500)
    set @sql = 'BCP "SELECT tbBin FROM AdventureWorks2008R2.dbo.TestBlob where tbId=3" QUERYOUT C:\blob\ddoc\81.pdf -T -f C:\blob\testblob.fmt -S ' + @@SERVERNAME
    EXEC master.dbo.xp_CmdShell @sql

    SET @sql = 'BCP "SELECT tbBin FROM AdventureWorks2008R2.dbo.TestBlob where tbId=4" QUERYOUT C:\blob\ddoc\mountain.jpg -T -f C:\blob\testblob.fmt -S ' + @@SERVERNAME
    EXEC master.dbo.xp_CmdShell @sql

    SET @sql = 'BCP "SELECT tbBin FROM AdventureWorks2008R2.dbo.TestBlob where tbId=5" QUERYOUT C:\blob\ddoc\Questionnaire.docx -T -fC:\blob\testblob.fmt -S ' + @@SERVERNAME
    EXEC master.dbo.xp_CmdShell @sql

    SET @sql = 'BCP "SELECT tbBin FROM AdventureWorks2008R2.dbo.TestBlob where tbId=6" QUERYOUT C:\blob\ddoc\txpeng542.exe -T -f C:\blob\testblob.fmt -S ' + @@SERVERNAME
    EXEC master.dbo.xp_CmdShell @sql

8 comments:

Ricky Smith said...

Great
Mifare readers
Mifare cards

Anonymous said...

Thank you for the clear instructions, I tried this technique and am close but not quite there. I am trying to export out of a SQLIMAGE field in a SQL 2005 database. My format file looks like:
9.0
1
10 SQLIMAGE 0 0 "" 10 image_thumbnail

When I run BCP with queryout I get an error message saying 'Error = [Microsoft][SQL Native Client]Incorrect host-column number found in BCP format-file'

My field name does not have an empty space so I don't understand why I am getting this error.

makdns said...

10 SQLIMAGE 0 0 "" 10 image_thumbnail

suppose to be "1" not "10" at starting, because you are inserting only one field

1 SQLIMAGE 0 0 "" 10 image_thumbnail

Anonymous said...

OK, I made that adjustment, now it throws this error:
Error = [Microsoft][SQL Native Client]Server column order must match field order of file for BCP with QUERYOUT

FYI - The image_thumbanail field is the 10th field in the table that I am selecting from. Does the format file think it is the first?

JIN said...

Remember to edit also column 6 in your format file. Column 1 and 6 must contain the same number...

JIN said...

You must also edit column 6 from 10 to 1:
1 SQLIMAGE 0 0 "" 1 image_thumbnail

Anonymous said...

Success!! Thank you.

Anonymous said...

Worked perfect for me, without any modifications required.