Tuesday, May 16, 2017 9:56 AM
I don't do much SQL in regular day to day basis but when it comes to it then it gets really exciting. Here is one of the odd days where I wanted to push out a image file from SQL which was sorted as varbinary(MAX) in database. As you all know that bcp is a very handy utility when it comes to dumping data out. So, I made that as a first choice but soon realized it was difficult to handle varbinary with the default arguments. Reading the internet here is what I could learn...
You need a format (.fmt) file for such an export. To generate the format file you need first go to the command prompt and perform the following:
D:\>bcp "select top 1 annotation from [DEMO_0]..[MasterCompany_Demographics_files]" queryout d:\test.png -T
Enter the file storage type of field annotation [varbinary(max)]:{press enter}
Enter prefix-length of field annotation [8]: 0
Enter length of field annotation [0]:{press enter}
Enter field terminator [none]:{press enter}
Do you want to save this format information in a file? [Y/n] y {press enter}
Host filename [bcp.fmt]: annotation.fmt {press enter}
Starting copy...
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)This will help you to generate your format file which then can be used to export out the images easily.
D:\>bcp "select top 1 annotation from [DEMO_0]..[MasterCompany_Demographics_files]" queryout "d:\test.png" -T -f annotation.fmt
Starting copy...
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (66.67 rows per sec.)
I hope this helps someone...