从SQL Server varbinary列临时检索数据

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

我想从SQL Server数据库中的varbinary(max)列中检索一些二进制数据以进行调试。

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.

我已经尝试使用SQL Server Management Studio(带有“results to file”选项),但是这会将十六进制编码的二进制字符串输出到文件,而不是原始二进制数据。

3 个解决方案

#1

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

我用bcp命令找到了这个解决方案(从命令提示符运行):

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.

我使用-T选项使用Windows身份验证连接到数据库。如果使用密码验证,则需要使用-U和-P开关指定用户名和密码。

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

但我也喜欢LinbPad在Robb Sadler的回答中提出的建议并且不知何故更喜欢它。

#2

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

我想不出更容易做到这一点比扔掉一点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.

此代码已在SQL Server与Reporting Services的默认安装中使用Users.Sid列(varbinary类型)进行了测试。

#3

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的建议。我尝试了它,并有一个查询,在10分钟内将二进制文件吐出到一个文件。没有VS项目,没有构建 - 现在脚本已保存,我可以随时将其拉出来。非常酷!

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());

#1