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

2 Comments

  1. Hi, I’m using the first method above (OAMethod) to extract documents from a SQL database backend for Documotive, in preparation for migrating to SharePoint.

    The method works perfectly for pdfs, images, .doc and .xls files. However, as soon as it comes to .docx and .xlsx files, when you try to open them you get a message saying the file is corrupt; do you want to repair it. Clicking yes fixes the issue and the document looks fine. However we have around 2Tb of data to migrate, and physically opening all the .docx / .xlsx files to re-save them is impossible.

    Do you have any thoughts on why this might be occurring? I’ve pasted the relevant parts below.

    — Variable declarations done correctly above

    DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num] varchar(100) , [FileName] varchar(200), [Doc_Content] varBinary(max) )

    INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])

    — Large Select statement to get relevant docs from different parts of the database. Snipped because mostly irrelevant

    SELECT @i = COUNT(1) FROM @Doctable
    WHILE @i >= 1

    BEGIN
    SELECT
    @data = [Doc_Content],
    @fPath = @outPutPath + ‘\’+[FileName],
    @folderPath = @outPutPath ,
    @doc_num = doc_num
    FROM @Doctable WHERE id = @i

    –Create folder first
    EXEC [dbo].[CreateFolder] @folderPath

    EXEC @hr = sp_OACreate ‘ADODB.Stream’, @init OUTPUT; — An instace created
    IF @hr 0
    BEGIN
    RAISERROR(‘Error %d ODODB.Stream.’, 16, 1, @hr)
    RETURN
    END

    EXEC @hr = sp_OASetProperty @init, ‘Type’, 1;
    IF @hr 0
    BEGIN
    RAISERROR(‘Set Property’, 16, 1, @hr)
    RETURN
    END

    EXEC @hr = sp_OAMethod @init, ‘Open’; — Calling a method
    IF @hr 0
    BEGIN
    RAISERROR(‘Open’, 16, 1, @hr)
    RETURN
    END

    EXEC @hr = sp_OAMethod @init, ‘Write’, NULL, @data; — Calling a method
    IF @hr 0
    BEGIN
    RAISERROR(‘SWrite’, 16, 1, @hr)
    RETURN
    END

    EXEC @hr = sp_OAMethod @init, ‘SaveToFile’, NULL, @fPath, 2; — Calling a method
    IF @hr 0
    BEGIN
    insert into [dbo].[Extracted_Data_Files_errors] ([id],[filename],[date]) values (@doc_num,@fPath,getdate());
    RAISERROR(‘SaveToFile’, 16, 1, @hr)
    END
    else

    insert into [dbo].[Extracted_Data_Files] ([id],[filename],[date]) values (@doc_num,@fPath,getdate());

    EXEC @hr = sp_OAMethod @init, ‘Close’; — Calling a method
    IF @hr 0
    BEGIN
    RAISERROR(‘Close’, 16, 1, @hr)
    RETURN
    END

    EXEC sp_OADestroy @init; — Closed the resources

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.