Vaibhav Gaikwad
Microsoft .NET developer
Bla Code! Bla Code!

MS SQL - bcp to export varbinary to image file

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...



Feedback

# re: MS SQL - bcp to export varbinary to image file

Sending Flowers and Gifts to your Mother on Mother’s day is the best way to express your feelings of appreciation for all the years of struggle that she put in to your raising. They show you care, that you love her and that you're there for her. Send Mother's Day Flowers to Malaysia and give joy and happiness to you loved ones. Send Mother's Day Gifts to Malaysia and convey the feelings of your heart. 5/24/2017 2:20 PM | Urgan Lama

Post a comment