Ad-hoc retreival of data from SQL Server varbinary column

Question

I would like to retreive some binary data from a varbinary(max) column in a SQL Server database for debugging purposes.

What is the easiest way to get this data into a local binary file, preferably without having to write a throw-away console application?

I have tried using SQL Server Management Studio (with the "results to file" option) but this outputs a hex encoded binary string to the file, rather than the raw binary data.

I'm not sure if this is what you were looking for, but do you know about LINQPad? It's basically an application in which you can write "throw-away" C# applications. — Deniz Dogan, Apr 28 '10 at 09:04

Daniel Renshaw · Answer 1 · 2010-04-28T09:20:39.457

I can't think of any easier way to do this than a throw away bit of C#...

    static void Main(string[] args)
    {
        GetBinaryDataToFile("Server=localhost;Initial Catalog=ReportServer;Integrated Security=true", "D:\\temp.dat");
    }

    public static void GetBinaryDataToFile(string connectionString, string path)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "SELECT Sid FROM Users WHERE UserID = '62066184-8403-494E-A571-438ABF938A4F'";
                command.CommandType = CommandType.Text;

                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        SqlBinary sqlBinary = dataReader.GetSqlBinary(0);
                        File.WriteAllBytes(path, sqlBinary.Value);
                    }

                    dataReader.Close();
                }
            }

            connection.Close();
        }
    }

This code has been tested using the Users.Sid column (which is of varbinary type) in a default installation of SQL Server wih Reporting Services.

score 4 · Answer 2 · answered May 05 '11 at 14:20

4

I loved the LinqPad suggestion. I tried it and had a query that spit out the binary to a file within 10 minutes. No VS Project, no build - and now the script is saved and I can pull it up anytime. So cool!

LinqPad script:

var g = from pd in Archives 
    where pd.ArchiveId == 123
    select pd;

var q = from p in printDocs
where p.DocumentId == g.SingleOrDefault().DocumentId
select p;

File.WriteAllBytes("C:\\temp.pdf", q.SingleOrDefault().Pdf.ToArray());

answered May 05 '11 at 14:20

Robb Sadler

705
10
22

I don't get it. How does this work in LinqPad? Where is 'Archives' defined? Are you referencing some external libraries where your Linq-to-SQL/EntityFramework entities are defined? – Daniel Fortunov Jun 07 '11 at 14:40
I attached to my database and LinqPad built the Linq to Sql data class automatically. I then could access and use it. That is part of what makes it cool. It's a quick download - I recommend giving it a whirl. This was the only code I wrote. – Robb Sadler Jun 10 '11 at 14:09
@DanielFortunov See the step-by-step instruction written by Atron Seige in https://stackoverflow.com/questions/4056050/script-to-save-varbinary-data-to-disk – Tony Jan 06 '20 at 23:35

score 3 · Accepted Answer · edited May 23 '17 at 10:27

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.

Ad-hoc retreival of data from SQL Server varbinary column

3 Answers3