Script to Save Varbinary Data to Disk

Script to save varbinary data to disk

The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren't equivalent to what's stored. Perhaps BCP is writing some kind of header. I'm not sure.

I found the following code here at the bottom of the article. It works great! Although it was intended for stored BMP images, it works with any varbinary.

DECLARE @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
        SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations

OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH 

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'

        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, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

        FETCH NEXT FROM IMGPATH INTO @IMG_PATH 
    END 

CLOSE IMGPATH
DEALLOCATE IMGPATH

How to download a file from a varbinary column using T-SQL

EDIT: I just re-read your question and I think I've got the issue wrong...

The line So this table has multiple rows hooked me...

If you want nothing more then to get the binary content of a table's column out to the file system, you might use BCP (see the linked answer and keep in mind, that this will not act under your account, so access rights and target directories might not be what you expect). But I would use some external tool, probably powershell, connect to the database and simply read the content to a file.

I won't delete my former answer as it can help too.

I'm pretty sure, that T-SQL is the wrong tool for this. My answer might help you, it will at least show some principles...

A BLOB column (in your case VARBINARY(MAX)) is nothing more than a meaningless chain of bytes. SQL-Server has no idea how to interpret this as meaningfull data.

I use a simple string variable to mockup a file's content:

DECLARE @TestText VARCHAR(MAX)=
N'This is the first line
This is the second line';

--Now this string is casted to varbinary and assigned to a BLOB-variable.

--This is - in principles - what you've got within your column

DECLARE @YourFileStore VARBINARY(MAX)=CAST(@TestText AS VARBINARY(MAX));
SELECT @YourFileStore;

--This you get back

0x5468697320697320746865206669727374206C696E650D0A5468697320697320746865207365636F6E64206C696E65

--this is a HEX-string, where each two digits represent a character (the 54 is the T, the 68 the h and so on.

--Somewhere in the middle you'll find the sequence 0D0A, which is the line break.

--As long as we know how to interpret the binary, we can simply re-cast it to the former type. This will get the former string back:

SELECT CAST(@YourFileStore AS VARCHAR(MAX));

--Reading this line by line will need a string-splitting at the line breaks.

--You did not state your SQL-Server's version. Here I use OPENJSON (needs v2016+).

SELECT *
FROM OPENJSON('["' + REPLACE(CAST(@YourFileStore AS VARCHAR(MAX)),CHAR(13)+CHAR(10),'","') + '"]');

When will this work for you:

This might work for you, if all BLOBs have the same content (in structure) and SQL-Server can use a simple CAST or CONVERT to achieve a meaningfull data type.

When will this not work for you:

If the textfiles are not simple UCS-2 (almost the same as UTF-16), which translates to NVARCHAR(MAX) or plain ASCII, which translates to VARCHAR(MAX).

Any encoding (like utf-8), the wrong endian or ASCII >127 (look for collations) might return just rubbish.

What you should do:

If this simple cast helps you to read the data - well - then your are a lucky one. If not, you should use another tool with more abilities in string handling.

You should always store data in the appropriate type. BLOBs are meant for data, where you do not want to look inside (like a picture). Your data should be stored in separate lines, with a common ID and a row number to re-create the full text.

How to pull out data from varbinary(Max) in Microsoft SQL

Add a new variable of the same type as your filename column at the top.

Change your cursor to select both the varbinary and the filename, and use your new filename variable.

Use the filename variable when setting the filename in your script, instead of the date.

Your script becomes:

DECLARE @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT,
    @Filename varchar(max)

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
        SELECT csl_CompanyLogo, [filename] from mlm_CSCompanySettingsLocalizations

OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH , @Filename

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TIMESTAMP = 'd:\' + @Filename + '.bmp'

        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, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

        FETCH NEXT FROM IMGPATH INTO @IMG_PATH , @Filename
    END 

CLOSE IMGPATH
DEALLOCATE IMGPATH

Saving binary data as file using JavaScript from a browser

This is possible if the browser supports the download property in anchor elements.

var sampleBytes = new Int8Array(4096);

var saveByteArray = (function () {
    var a = document.createElement("a");
    document.body.appendChild(a);
    a.style = "display: none";
    return function (data, name) {
        var blob = new Blob(data, {type: "octet/stream"}),
            url = window.URL.createObjectURL(blob);
        a.href = url;
        a.download = name;
        a.click();
        window.URL.revokeObjectURL(url);
    };
}());

saveByteArray([sampleBytes], 'example.txt');

JSFiddle: http://jsfiddle.net/VB59f/2

Ad-hoc retreival of data from SQL Server varbinary column

I've found this solution with bcp command (run from command prompt):

c:\temp>bcp "select MYVARBINARYCOL from MYTABLE where id = 1234" queryout "c:\filename.pdf" -S MYSQLSERVER\MYINSTANCE -T

Enter the file storage type of field filedata [varbinary(max)]:
Enter prefix-length of field filedata [8]: 0
Enter length of field filedata [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (66.67 rows per sec.)

I used the -T option to use windows authentication to connect to the DB. If you use password auth, you'll need to use the -U and -P switches to specify a username and password.

But I also like LinqPad suggestion in Robb Sadler's answer and somehow prefer it.

fastest way to export blobs from table into individual files

I tried using a CLR function and it was more than twice as fast as BCP. Here's my code.

Original Method:

SET @bcpCommand = 'bcp "SELECT blobcolumn FROM blobtable WHERE ID = ' + CAST(@FileID AS VARCHAR(20)) + '" queryout "' + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand

CLR Method:

declare @file varbinary(max) = (select blobcolumn from blobtable WHERE ID = @fileid)
declare @filepath nvarchar(4000) = N'c:\temp\' + @FileName
SELECT Master.dbo.WriteToFile(@file, @filepath, 0)

C# Code for the CLR function

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

namespace BlobExport
{
    public class Functions
    {
      [SqlFunction]
      public static SqlString WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append)
      {        
        try
        {
          if (!binary.IsNull && !path.IsNull && !append.IsNull)
          {         
            var dir = Path.GetDirectoryName(path.Value);           
            if (!Directory.Exists(dir))              
              Directory.CreateDirectory(dir);            
              using (var fs = new FileStream(path.Value, append ? FileMode.Append : FileMode.OpenOrCreate))
            {
                byte[] byteArr = binary.Value;
                for (int i = 0; i < byteArr.Length; i++)
                {
                    fs.WriteByte(byteArr[i]);
                };
            }
            return "SUCCESS";
          }
          else
             "NULL INPUT";
        }
        catch (Exception ex)
        {          
          return ex.Message;
        }
      }
    }
}

---

Related Topics