SQL Server – BLOB Import and Export – CodeProject.

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:

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.

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:

If you get error, use the second option:

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

Open the format testblob.fmt file, this FORMAT file mapped the whole testblob table fields.

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:

Execute the below script to export, 4 exported (pdf, doc, image and exe) files in the ddoc folder.

License

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

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注