SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Binary Files Import and Export


Binary Files Import and Export

Author
Message
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3432 Visits: 3029
Another option is to use CLR, even though not everyone is a fan of that option. The SQL# library that is available at http://www.SQLsharp.com/ has functions to do this. Since those functions are not available in the free version, I can say that you can accomplish this by using the BinaryReader and BinaryWriter classes and the SqlBytes datatype to map to VARBINARY(MAX).

SQL# - http://www.SQLsharp.com/
DeafEater
DeafEater
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 260
Another option is to use CLR, even though not everyone is a fan of that option. The SQL# library that is available at http://www.SQLsharp.com/ has functions to do this. Since those functions are not available in the free version, I can say that you can accomplish this by using the BinaryReader and BinaryWriter classes and the SqlBytes datatype to map to VARBINARY(MAX).

Thanks I'll try that.

Regards

Robert
sergey.benner
sergey.benner
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 164
Robert (8/28/2008)
I'm also having trouble implementing this without corruption of the file after it's undergone a round trip. I am importing a text file using OPENROWSET and as far as I can see that arrives in the varbinary field perfectly. The query:

SELECT DATALENGTH(FileData) FROM TestFiles

returns a number exactly equal to the test file size.

However when I output it back to a file using the options you specify for bcp, I get a file that is 4 bytes longer than the original. The 4 extra bytes are right at the beginning of the file before the expected data starts and are as follows (in hex):

B5 42 00 00

Any ideas?




OK. Here is the answer for this I've done some research during the spare time.
The research is called RTFM Smile

The article here called

http://msdn.microsoft.com/en-us/library/ms191212.aspx

The MSSQL2005-2008 create the 8 byte prefix at the beginning of the file
during the bulk export. To avoid it - use the .fmt file.

My bcp.fmt file looks like this:


9.0
1
1 SQLBINARY 0 0 "" 1 data ""



Then the bcp command :


bcp "SELECT data FROM testdb.dbo.data WHERE file_id=1" queryout 0.dat -T -f bcp.fmt

That would be the solution.
DeafEater
DeafEater
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 260
Great - that was indeed the answer EXCEPT that I had to have 8.0 at the top of the format file rather than 9.0 (my output file had a 4 byte prefix rather than 8 bytes).

Thanks for your help.

Robert
areichow
areichow
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 24
EDIT: Wow... This was boneheaded of me. I didn't see there were two other pages after the first, in which DE's questioned were eventually answered. Leaving the rest of my post here since I can't delete it outright...

Apologies for the necroposting, but this thread came up a handful of times when I was googling around trying to find a solution to the very same problem.

In short, DeafEater's problem is real, not imagined. I've had the same problem, and it doesn't matter whether I'm connecting to SQL Server 2000, 2005, or 2008. It doesn't matter what version of the client tools I'm using.

In my case, I'm importing and exporting binary files, mostly ZIPs. I'm importing files into the table like so:


CREATE TABLE FileStore (
[id] [int] IDENTITY(1,1) NOT NULL,
[filename] [varchar](255) NULL,
[created] [datetime] NULL,
[filedata] [varbinary](max) NULL,
)

insert FileStore (filename, created, filedata) select '$(_FILENAME)', GETDATE(), BulkColumn from Openrowset ( Bulk '$(_FILENAME)', Single_Blob) as filedata



Originally, I exported ZIP files from the database with:


bcp "select top 1 filedata from dbiutil.dbo.FileStore order by created desc" queryout test01.zip -S fssrv -T -n



Which appeared to work fine, but test01.zip was always four bytes larger than it should be- and WinZip wasn't able to open it. Later, I figured out that Info-ZIP (zip.exe and unzip.exe) and 7zip (7za.exe) had no problems extracting the ZIP. However, Info-ZIP gives this warning:


Archive: test0.zip
warning [test0.zip]: 4 extra bytes at beginning or within zipfile
(attempting to process anyway)
creating: test01/
inflating: test01/a
inflating: test01/b



Naturally, this stumped me. After running into a few forum threads here and elsewhere, I tried doing the BCP without the -n (native type) switch...


c:\bcptesting>bcp "select top 1 filedata from dbiutil.dbo.FileStore order by created desc" queryout test02.zip -S fssrv -T

Enter the file storage type of field filedata [image]:
Enter prefix-length of field filedata [4]: 0
Enter length of field filedata [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]:

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 6000



This time, test01.zip had no extra bytes, and I was able ot open it in WinZip, Info-ZIP, and 7zip without errors or warnings. Indeed, the md5sum hash of the test02.zip that I exported with this method was the same as the original file.

It should be obvious to anyone reading this that those four extra bytes is the "prefix-length of field filedata" mentioned above. By specifically telling bcp to use 0 things are imported and exported without any corruption or added bytes. Since running through this interactively is hardly what we want, just take the resulting bcp.fmt and use it for any future exports...


c:\cbtesting>bcp "select top 1 filedata from dbiutil.dbo.FileStore order by created desc" queryout test03.zip -S fssrv -T -f bcp.fmt

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 5922



Again, test03.zip has the same md5sum hash as the original file and test02.zip.

It's a bit tedious to have to ship an extra file like this, but at least there's some solution. It works the same way no matter what version of SQL Server you're using from 2000 on; same goes for the client tools. Microsoft forgot to add a parameter for bcp.exe for specifying the "prefix-length of field filedata," forcing you to run through the manual process once first.

BCP doesn't have a bug, it's just poorly designed. Tongue

Aaron
wjones20
wjones20
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 3
Hey,

I'm having the exact same problem and just posted a detailed description at MSDN forums yesterday [/url] Unfortunately, I haven't gotten any answers there yet.

I found a posting this morning that suggested the problem is that the extra characters are because the BCP QUERYOUT utility is adding the file length at the beginning of the VARBINARY field contents before reading it in the external file. The solution is supposed to be creating format files, though I don't know if you need a different file for each file type (PDF, XLS, DOC, etc.) Here's the posting [url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113071]


I'll post something back if I can get this to work.

Good luck,
Bill
wjones20
wjones20
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 3
Hi,

I'm having the exact same problem. Here's my post on it over in MSDN forums [/url]

I found some postings elsewhere that suggested the problem is the BCP QUERYOUT is adding the file length to the beginning of the contents--those are the extra characters [url=http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/29f30130-3a64-4b91-86b3-5fa09dbc4220/]
[url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113071][/url]

Apparently the solution is to specify a format file during your output that stops the utility from adding the file length data. I'm going to play with that in the next day or two and will post back if I find something.

Good luck,
Bill
sergey.benner
sergey.benner
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 164
You should read the whole thread first because it has been answered years ago.
wjones20
wjones20
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 3
Hey, thanks. Yes, the interface was very confusing. I responded twice because I was Replying to the post at the bottom of Page 1 and it kept refreshing without showing my reply appended the post I was replying to. I will study the second and third pages carefully. Cheers, Bill
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search