Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Binary Files Import and Export Expand / Collapse
Author
Message
Posted Thursday, September 4, 2008 2:27 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
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/
Post #564172
Posted Friday, September 5, 2008 1:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:11 AM
Points: 143, Visits: 243
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
Post #564368
Posted Monday, September 15, 2008 4:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
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 :)

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.
Post #569863
Posted Monday, September 22, 2008 2:42 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:11 AM
Points: 143, Visits: 243
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
Post #573314
Posted Wednesday, April 7, 2010 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 3, 2010 8:20 AM
Points: 1, 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. :P

Aaron
Post #898737
Posted Thursday, May 9, 2013 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 9, 2013 11:52 AM
Points: 3, 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
Post #1451091
Posted Thursday, May 9, 2013 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 9, 2013 11:52 AM
Points: 3, 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
Post #1451105
Posted Thursday, May 9, 2013 9:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:07 AM
Points: 210, Visits: 160
You should read the whole thread first because it has been answered years ago.
Post #1451208
Posted Thursday, May 9, 2013 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 9, 2013 11:52 AM
Points: 3, 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
Post #1451257
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse