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

Question

I am working with SQL Server 2016. I have a table file_data which has a column data of varbinary type.

CREATE TABLE [dbo].[file_data]
(
    [file_data_id] [INT] IDENTITY(1,1) NOT NULL,
    [file_name] [VARCHAR](20) NOT NULL,
    [description] [VARCHAR](255) NOT NULL,
    [data] [VARBINARY](MAX) FILESTREAM  NOT NULL,
    [file_guid] [UNIQUEIDENTIFIER] ROWGUIDCOL  NOT NULL,

    CONSTRAINT [pk_file_data] 
        PRIMARY KEY CLUSTERED ([file_data_id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [filestream],
    UNIQUE NONCLUSTERED ([file_guid] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [filestream]
GO

That data column stores 1 file usually a txt file. So this table has multiple rows.

How can I download file of any 1 row from the data column using T-SQL? I am using Microsoft SQL Server Management Studio.

You can't get binary information out that way, at least not easily, its not designed for that. You would normally pull files from code. — Dale K, Apr 03 '19 at 01:35
try the solution here https://stackoverflow.com/questions/4056050/script-to-save-varbinary-data-to-disk — Squirrel, Apr 03 '19 at 03:28

Shnugo · Accepted Answer · 2019-04-03T07:34:38.717

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.

Thanks for your detailed answer. It work because the column contains text files. — srh, Apr 07 '19 at 14:39