Category: Data Engineering

It’s a common understanding that BLOB column is used to store documents. If we are not using File stream feature, then the data is stored in primary file group and storage limit is 2GB. This approach is handy but slows down database performance and takes significant resources to read data.

Here is a quick example;

--create a new blob table
CREATE TABLE [dbo].[TraditionalBlobTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BlobDataCol] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Insert some data in this table, i am inserting excel file;

--insert excel file in table
INSERT TraditionalBlobTable([BlobDataCol])
SELECT [Doc_Data].*
FROM OPENROWSET
	(BULK 'C:\Test.xlsx', SINGLE_BLOB)  [Doc_Data]

SELECT * FROM [ToDo].[dbo].[TraditionalBlobTable]

Let’s see how many tables have blob data column in our database;

--how to identify all sql server tables with blob columns
SELECT o.[name], o.[object_id ], c.[object_id ], c.[name], t.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE c.system_type_id IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.[type] = 'U'
GO

We are using OLE Automation here to get this data out and store in a file. The first step is to turn on OLE automation procedure;

--turn on ole automation procedure
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO

Now to the real meat, creating file on file system;

declare @init int
declare @file varbinary(max);
SELECT @file = BlobDataCol FROM TODO.dbo.TraditionalBlobTable

declare @filepath nvarchar(4000) = N'c:\business\myfile.xlsx'

EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1; 
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @file; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @filepath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources

Check you file system, the file should be there;

As a security best practice, turn off OLE automation procedure;

--turn off ole automation procedure
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 0;  
GO  
RECONFIGURE;  
GO

Resources

https://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

Saving and Extracting BLOB Data – Basic Examples

http://www.jitendrazaa.com/blog/sql/sqlserver/export-documents-saved-as-blob-binary-from-sql-server/