Click here to Skip to main content
16,020,313 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

SQL Server - BLOB Import and Export

Rate me:
Please Sign up or sign in to vote.
4.82/5 (3 votes)
24 Apr 2012CPOL2 min read 141.5K   15   3
SQL scripts to insert File to BLOB field and export BLOB to File

Introduction 

SQL scripts to insert File to BLOB field and export BLOB to File. Using SQL Server 2008, you can save images / files to BLOB binaries and retrieve them back to the file system.

Import 

Test table structure:
SQL
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]    
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.
SQL
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

Export 

To export a 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 the DOS command line or within SQL Server Management Studio query window.

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:
SQL
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
If you get error, use the second option:
SQL
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.
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.

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 
Open the format testblob.fmt file, this FORMAT file mapped the whole testblob table fields.
SQL
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:
SQL
10.0
1
1       SQLBINARY           0       0       ""   1     tbBin            ""
Execute the below script to export, 4 exported (pdf, doc, image and exe) files in the ddoc folder.
SQL
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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionnot work for some blob has been manipulated Pin
lookilok4-Apr-23 20:19
lookilok4-Apr-23 20:19 
This code works correctly for standard blob.

But it doesn't work for the data which left 100 starts with the path of the file in the blob.
for example doc file:
left(FileImage,100) is equal to

D:\AppGen\AutoTemps\TmpEdit210.doc
For data whose type is doc.[binary start with 0x443A5C]

It seems that this blob has been manipulated.
when I open this document, the content is corrupted. As below

<pre>D:\AppGen\AutoTemps\TmpEdit210.doc                                                                                                                                                                                                                                   ì  
S  xœì½<•ïÿøƒcï=;$ddïì-D$ ¶Œlʈ̖–B‘B[S¤=ÌÒ 


How can I retrieve the doc file in this blob safely?
best regards.
QuestionAlmost there Pin
Member 102547065-Sep-13 2:53
Member 102547065-Sep-13 2:53 
QuestionA Question Pin
magicpapacy1-May-12 17:53
magicpapacy1-May-12 17:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.