Additional bytes being prepended to file when saved to SQL Server

Question

I receive a file through a SFTP session in a WCF service. This file is being read into a MemoryStream object. I've output this stream successfully to a file on disk & verified that the file is transferred successfully (this was done using a FileStream instead of the MemoryStream).

But when I convert this MemoryStream to a byte[] to save it to a SQL Server table (with the column declared as VARBINARY(MAX)), it seems that 8 bytes are being prepended to the file.

As an example, I've uploaded a .txt file using the WCF SFTP Service & saved it to the database. Then, using BCP, I've output the file to disk. Upon opening the file, the first line has "U " prepended to it (a "U" and 7 spaces).

The same happens to Office documents. I've done the same procedure as mentioned above for a .xls file, which at the start is 49Kb big. But upon outputting it using BCP, the file is corrupt & is 50Kb big.

Oddly, .pdf files seem to save & export correctly.

A few snippets of the code I'm using:

To convert the stream to a byte array

var stream = (MemoryStream)data;
stream.Seek(0, SeekOrigin.Begin);
byte[] m_Bytes = stream.ToArray();

To save the byte array to SQL Server

cmd.Parameters.Add("@File", System.Data.SqlDbType.VarBinary, -1).Value = file;

To export the file from SQL Server to a file using BCP

bcp "SELECT [File] FROM SFTPUpload.dbo.Upload" queryout "C:\SFTP\Test.txt" -T -N -S Server_Name\Instance_Name

Could this be a problem with the method I'm using to save the file to the database, or how I'm retrieving the file using BCP? The latter seems more probable, since saving the file directly to disk does not corrupt it.

Any help or advice would be greatly appreciated.

Answer 1

I just had the same issue, and here's how to fix it.

In my example, I had a SQL Server table called "External_File" and one of its fields, ExtFile_Data, was a varbinary(max) containing raw file data, usually of an entire Excel file.

What I wanted to do was use bcp to quickly export this raw data into a file on our database server.

This command almost worked perfectly...

--  This does not work - don't use it !!
EXEC master..xp_cmdshell 'BCP "select ef.ExtFile_Data 
   FROM [External_File] ef  WHERE ExtFile_ID = 1005" 
   queryout "G:\ImportData\TestFile.xlsm" -T -N'

...but it added 8 bytes at the start of each file, so none of them could be opened.

The solution was to make bcp use a formatting file, which I called "BCPformat.fmt"...

EXEC master..xp_cmdshell 'BCP "select ef.ExtFile_Data 
   FROM [External_File] ef  WHERE ExtFile_ID = 1005" 
   queryout "G:\ImportData\TestFile.xlsm" -T 
   -f "G:\ImportData\BCPformat.fmt" '

Here's what my BCPformat.fmt file looked like.

10.0
1
1 SQLBINARY 0 0 "" 1 ExtFile_Data ""

Note that your formatting file must match the structure of data you're attempting to export.

In my case, I was exporting just one column, called ExtFile_Data, so this is the field name in my .fmt file. Your field names will be different, so your .fmt file will need to reflect this.

But. with this .fmt file in place (and accessible by whichever SQL Server user is running the script), I could successfully export raw data into an Excel file, without those extra 8 bytes at the start.

And this method is hugely faster at turning large chunks of raw SQL Server data into a real file than any other method I could find.

It's just ironic that bcp doesn't have a simple flag to dump a varbinary(max) field into a file without adding anything onto this chunk of data.

Hope this helps.

Answer 2

BCP writes out data in its BCP format. The BCP format must contain more than just the binary data because it supports multiple columns and rows. Export the binary data using a method that preserves its bytes exactly.

My guess is that the initial 8 bytes are the blob length.