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.
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.