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:
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.
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:
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:
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.
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 ""
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
