Reading binary file to varbinary field loses first 4 bytes of each record

• drcrank

  SSC Rookie

  Points: 39

  More actions

  April 30, 2008 at 7:06 am

  #121276

  FYI: This is resolved - Here's the resolution:

  By adding 4 bytes of spaces (i.e 0x20202020) to the start of each record, by reading one record's length of data in bytes, SQL Server works perfectly.

  I'm going nuts!

  I have a very old database on a SCO-UNIX box, which stores its data in binary files. I've decided to read the files into SQL Server 2000, in a varbinary field in a staging table, and then use SUBSTRING to extract the fields I'm interested in.

  Here's the problem:

  Each file has a header of 128 bytes. I use VBScript to skip the first 128 bytes, and then write the remainder to a new text file. Each record is 256 bytes. I've confirmed that I've extracted the right part of the file by

  a) dividing the total file size in bytes by the number of bytes per record (this works)

  b) using a hex editor to verify the contents of the file (this works)

  I then try to BCP the data into a staging table with 2 fields, "ID" which is an int IDENTITY field, and SOURCE_DATA which is a varbinary(8000) field.

  If I put in the SOURCE_DATA field length at 256 - it doesn't work. I need to put it to 4 bytes less (252) and then it works (all the data is aligned) but the first 4 bytes of each row are missing.

  This is the same for every one of these files I try to load.

  Any ideas?

  Thanks