SQL Server Varbinary Example – To and From A Flat File

Even though SQL Server and MS Word share the same parent (Microsoft), it is not always easy to get them to play together nicely.

Fortunately the VARBINARY data type was introduced with SQL Server 2005.

In this article we will show how this datatype can be used to store word documents.

To prepare for this example we will need to create a word doc, and place in a folder, that is accessible by our SQL instance.

Make sure the word doc has some data in it, or this will not work!

Now run the code below. (After editing the path/name of the word document).

SQL Server Varbinary Insert Example

-----------------------------
CREATE TABLE ##VarbinaryExample (id int, DocData VARBINARY(MAX))

INSERT INTO ##VarbinaryExample
SELECT 1, bulkcolumn
FROM OPENROWSET( BULK 'C:\Temp\DBAFire-top-SQL Server Website.docx', SINGLE_BLOB ) AS y 
-----------------------------

Wasn’t that amazing! We actually got these two strangers to talk to each other!

The next thing to do is to attempt to export the document, from the database, back to a flat file.

SQL Server Varbinary to File



DECLARE @SQLIMG VARBINARY(MAX),
    @DOC_PATH VARCHAR(50) = 'C:\temp\'  ,
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT,
    @FILENAME VARCHAR(50) = 'NewFile.docx',
	@TOTALPATH VARCHAR(100) 

	SET @TOTALPATH = @DOC_PATH + @FILENAME
     
    SELECT @SQLIMG = DocData from ##VarbinaryExample
     
        --PRINT @TIMESTAMP
        --PRINT @SQLIMG
 
        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @SQLIMG  --new variable here
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TOTALPATH, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

		

Note: if there are any issues with running the above you may need to run this to configure for Ole.


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

The method we have used above is one way of doing it.

BCP Export a Word Document from Varbinary field

It can also be accomplished by using BCP to export the file.

For this exercise we will also need a format file.

This should be created in notepad, we can use the below data.

Note: Sometimes you will need to add an extra line at the bottom of it to avoid an i/o error.

We will need to take note of where we save the file and use the file path in the BCP command string given further down.

9.0
1
1 SQLBINARY 0 0 “” 1 doc “”

format file

This should be run in a command window.

Note: Before running this the earlier query that creates the temporary table will need to have been run.


bcp "SELECT DocData from ##VarbinaryExample" queryout "C:\temp\BCPNewfile.docx" -f "c:\temp\worddoc.fmt" -T -C -S localhost 

-T -c -C RAW -SMYPC

We now have created two examples that can be used to export a file from a binary data field.

Hopefully this article easy enough to follow – let us know.

If there are any questions, or any problems following the instructions, feel free to reach out and we will help.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply