Introduction

Binary Large Objects (BLOB) data can be a graphical image, a pdf document, a music file or any of a wide range of data types, which can generally be saved into a SQL Server database.

As part of a series of investigations I’m currently performing within SQL Server, I have looked at how BLOB data can be saved and retrieved.

Saving the data is reasonably simple. Retrieving it, less so….

Background

I have never needed to save BLOB data within a SQL Server database. There are various arguments for and against storing such items within a database but I’m not going to concern myself with that in this article; I just want to learn how it can be done.

Detail

For this example, I’m going to save a jpg image into a database and then extract it in a variety of ways:

1. BCP via SQL Server Management Studio (SSMS)
2. BCP in a command window
3. OLE object creation via SSMS
4. PowerShell

The code used in this article can be downloaded from here.

Preparing the Example

The first step is to create a test database, with a table that has a varbinary(max) column within it.

Listing 1: SQL code to create database and table

CREATE DATABASE BLOB_Test;
GO

USE BLOB_Test;
GO

CREATE TABLE dbo.PicturesTest
(
    ID INT IDENTITY(1, 1),
    PictureName VARCHAR(50) NOT NULL,
    PictureData VARBINARY(MAX) NOT NULL
);

GO

Saving the Image to the Database

In this example, I’m saving a jpg by using OPENROWSET to save the SINGLE_BLOB. An additional column has a suggested name for the image file, which will be used later, within the PowerShell example.

Listing 2: Storing the image

USE BLOB_Test;
GO

INSERT INTO dbo.PicturesTest
(
    PictureName,
    PictureData
)
SELECT 'Wile_E_Coyote.jpg',
       BulkColumn
FROM
    OPENROWSET(BULK 'C:\BLOBTest\BLOBIn\Oops.jpg', SINGLE_BLOB)
    AS BLOB;

GO

The result of this is a binary column containing the image data.

Image 1: Result of Listing 2

BCP via SQL Server Management Studio (SSMS)

The Bulk Copy Program (BCP) utility can be used to copy data between SQL Server and data files.
Using this within SSMS requires the use of xp_cmdshell, which in turn requires that xp_cmdshell is permitted within SQL Server. Not all sites allow this, so this option might not be permitted.
Exporting via BCP can be problematic and is improved by using a ‘format file’. This example has a format file supplied (‘BLOB.fmt’).

The SQL code to save the image data from SQL Server, to a file is:

Listing 3: Saving the image data to a file

USE BLOB_Test;
GO

DECLARE @sql VARCHAR(1000);
SET @sql
    = 'BCP "SELECT PictureData FROM BLOB_Test.dbo.PicturesTest " QUERYOUT C:\BLOBTest\BlobOut\WC.jpg -T -f "C:\BLOBTest\FormatFile\BLOB.fmt" -S ' + @@SERVERNAME;

 SELECT @sql;

EXEC master.dbo.xp_cmdshell @sql;

If xp_cmdshell is not permitted, you will receive the message:
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.

To enable xp_cmdshell, the easiest method is to execute sp_configure, which changes the settings for the entire instance, not just the database that we’re using.

Listing 4: Enabling xp_cmdshell

EXEC sp_configure 'Show Advanced Options',1;
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE;
GO

If this completes without any errors, execute the code in Listing 2 again and a file will be created in the target folder.

Image 2: Result of successful Listing 3

BCP in a command window

Where it isn’t possible to activate xp_cmdshell, BCP can be executed from a command window.
Firstly, ensure the BLOBOut folder is empty, so we know that this command completed successfully.
Make sure that you replace ‘ServerName’ and ‘InstanceName’ with values appropriate for your environment.

Listing 5: BCP in a CMD Window

BCP "SELECT PictureData FROM BLOB_Test.dbo.PicturesTest " QUERYOUT C:\BLOBTest\BlobOut\WC.jpg -T -f "C:\BLOBTest\FormatFile\BLOB.fmt" -S <ServerName>\<InstanceName>

Image 3: Result of Listing 5

OLE object creation via SSMS

As with using xp_cmdshell, this example requires that sp_configure is used to allow ‘Ole Automation Procedures’, otherwise a error message similair to the following will be raised:

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1 [Batch Start Line 2]
SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

If Ole Automation Procedures is not enabled and it is permitted to change this, then the code below will activate it. As with the activation of xp_cmdshell, this is an instance-wide command.

Listing 6: Reconfigure to allow Ole Automation Procedures

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

The example below saves the BLOB data to a specified file location and name by using a series of OLE objects. Check that the output location is empty, so you are sure that it has completed successfully.

Listing 7: Saving the BLOB data to a file via OLE processes

USE BLOB_Test;
GO

DECLARE @init INT,
        @Data VARBINARY(MAX),
        @FilePath VARCHAR(MAX);
BEGIN
    SELECT @data = PictureData,
           @FilePath = 'C:\BLOBTest\BLOBOut\WC.jpg'
    FROM dbo.PicturesTest;

    EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- Create Object
    EXEC sp_OASetProperty @init, 'Type', 1;
    EXEC sp_OAMethod @init, 'Open';
    EXEC sp_OAMethod @init, 'Write', NULL, @data;
    EXEC sp_OAMethod @init, 'SaveToFile', NULL, @FilePath, 2;
    EXEC sp_OAMethod @init, 'Close';
    EXEC sp_OADestroy @init; -- Destroy Object
END;

PowerShell

This example has been taken from a Technet article, with minor editing for the instance, file location and suchlike. It loops through the source table to extract all BLOB fields, using the ‘PictureName’ column to dictate the resultant filename. You will need to change the entry for $Database to the instance for your environment.

Listing 8: Saving the BLOB data to a file via PowerShell

## https://social.technet.microsoft.com/wiki/contents/articles/890.export-sql-server-blob-data-with-powershell.aspx
## Export of "larger" Sql Server Blob to file
## with GetBytes-Stream.
# Configuration data
$Server = ".\<Instance>";         # SQL Server Instance.
$Database = "Blob_Test";
$Dest = "C:\BLOBTest\BLOBOut\";             # Path to export to.
$bufferSize = 8192;               # Stream buffer size in bytes.
# Select-Statement for name & blob
# with filter.
$Sql = "SELECT [PictureName]
              ,[PictureData]
        FROM dbo.PicturesTest";            

# Open ADO.NET Connection
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$Server;" +
                        "Integrated Security=True;" +
                        "Initial Catalog=$Database";
$con.Open();            

# New Command and Reader
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;
$rd = $cmd.ExecuteReader();            

# Create a byte array for the stream.
$out = [array]::CreateInstance('Byte', $bufferSize)            

# Looping through records
While ($rd.Read())
{
    Write-Output ("Exporting: {0}" -f $rd.GetString(0));
    # New BinaryWriter
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write;
    $bw = New-Object System.IO.BinaryWriter $fs;            

    $start = 0;
    # Read first byte stream
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);
    While ($received -gt 0)
    {
       $bw.Write($out, 0, $received);
       $bw.Flush();
       $start += $received;
       # Read next byte stream
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);
    }            

    $bw.Close();
    $fs.Close();
}            

# Closing & Disposing all objects
$fs.Dispose();
$rd.Close();
$cmd.Dispose();
$con.Close();            

Write-Output ("Finished");

Image 4: Result of Listing 8

Summary

I’m not overly keen on the methods required to save or extract BLOB data with SQL Server but it does work. Hopefully one of the examples in this article should be suitable, depending upon the environment.

References

Technet – Saving BLOB Data with PowerShell
Format Files