July 25, 2016 at 1:44 pm
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.
July 25, 2016 at 5:14 pm
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
July 25, 2016 at 10:40 pm
Quick question, are you specifying either -c character type or -w wide character type
😎
July 27, 2016 at 12:10 pm
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.
July 27, 2016 at 2:26 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply