BCP command extracting varbinary files

Question

Post reply

BCP command extracting varbinary files

HildaJ

SSCarpal Tunnel

Points: 4516
More actions
July 25, 2016 at 1:44 pm

#320124

Good afternoon group,
I'm hoping I can get some help on this topic that I have research for several hours now. I have the need to export files that we have in our database as varbinary. The problem that I'm experiencing is the following:
Problem 1) When I use the command below, I get the files extracted from our DB but I get an error saying the file is too large or it is corrupted.
EXEC xp_cmdshell 'bcp "SELECT FileData FROM tableName WHERE FileID = 453456 " queryout "L:\FileData\' + @FileName + '" -T -n '
Problem 2) When I use a format file, I get the files extracted but they are all zero bytes (all are empty)
EXEC xp_cmdshell 'bcp "SELECT [FileData] FROM tableName WHERE FileID = 453456 " queryout "L:\FileData\' + @FileName + '" -T -fL:\Fileformatfile.fmt'
This process is working great only for files that I have stored as PDF but not on any of the other types such as tif, jpeg, doc, docx, or xls.
Any help or guidance would be great appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

What is the definition for your format file? I think you probably need to use a format file in your case.

Did you try specifying the prefix length as 0 in the format file. And field length 0 ?

Sue

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Quick question, are you specifying either -c character type or -w wide character type

😎

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 3

Sue_H,

I did created a format file, when I use that the file gets exported but it's empty, when I don't that's when I get a message that the file is too big or corrupted.

10.0

11

1 SQLCHAR 0 12 "," 1 FileID ""

2 SQLCHAR 0 255 "," 2 FilePath SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 12 "," 3 FileTypeID ""

4 SQLCHAR 0 255 "," 4 FileName SQL_Latin1_General_CP1_CI_AS

5 SQLCHAR 0 24 "," 5 DateUploaded ""

6 SQLCHAR 0 12 "," 6 PageNbr ""

7 SQLCHAR 0 0 "," 7 FileData ""

8 SQLCHAR 0 12 "," 8 UserID ""

9 SQLCHAR 0 37 "," 9 FileNetID ""

10 SQLCHAR 0 24 "," 10 DateEffective ""

11 SQLCHAR 0 3 "\r" 11 DrillingReportRollup ""

Eirikur,

I'm not using either -c or -w character type. I can do some testing and see what I get.

Thanks.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

That format file doesn't match up with your query you are using. The format file you have is for 11 columns but your query indicates you are only exporting one column. I am guessing you just want the one column of FileData. So the format file would look something like:

10.0

1

1 SQLBINARY 0 0 "" 1 FileData ""

for the query of SELECT [FileData] FROM tableName WHERE FileID = 453456

The second number listed is for the number of columns so it looks like the format file was probably created using a different query.

So if I use a format file like above and want to BCP out the gifs in the LargePhoto column of AdventureWorks2008.Production.ProductPhoto, I'd use that format file definition, change the column name FileData to LargePhoto and then use the following bcp command:

bcp "select LargePhoto from AdventureWorks2008.Production.ProductPhoto

where ProductPhotoID = 69"

queryout "C:\PathToBCPOutFiles\Test.gif" -S<YourServerName> -T -F"C:\PathToFormatFile\YourFormat.fmt"

Sue