Importing data from a binary file in SQL 2000

Question

Importing data from a binary file in SQL 2000

LongDriver

Old Hand

Points: 373
More actions
April 24, 2007 at 1:09 pm

#175938

Hi All,
I'm a beginning user trying to import a binary file of telemetry data. The records in the file are each 7291 bytes long (no delimiters). I created a table called "Raw Alink Records" with one field called "Raw Record" of type binary, length 7291, and no nulls allowed (yeah, I know the names are not very descriptive). I noticed that Bulk Insert seems to want delimiters, as does DTS in Enterprise Manager, so I tried bcp. Here is the command line I used (including responses to the formatting questions):
bcp "ICE.dbo.[Raw Alink Records]" in "c:\Alink.dat" -Sserver -Uuser -Ppwd
Enter the storage type of field Raw Record [binary]: x
Enter prefix-length of field Raw Record [1]: 0
Enter length of field Raw Record [7291]:
Enter field terminator [none]:
The format file generated looks like this:
8.0
1
1 SQLBINARY 0 7291 "" 1 Raw Record

When I try to run it, I get the following error:
SQLState = 37000, NativeError = 170 Incorrect Syntax near 'binary'
I tried hitting <enter> when it asked me for the storage type but that didn't work either (actually, I've tried quite a few different things). I am probably missing something obvious, but as this is the first time I've ever tried importing data into a SQL database I'm not sure what the problem might be. Any help would be greatly appreciated.
Thanks,
Mike

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Here is my suggestion without looking at the data, import the data into a temporary table as text (character data), then convert it as you move it to your database. The file you are importing from may also have line terminator, such as LF or CR/LF, and you will need to account for that also if they exist.

LongDriver Old Hand Points: 373 More actions · Answer 2

Thanks Lynn, I'll try that. Yes, it does have every byte from 0x00 to 0xFF; imagine a binary file of bytes of random value with each record being 7291 bytes long. Each record has many LFs, CRs, 0x00s, and other special characters randomly distributed throughout the record. There is no way to predict what positions they might be in, but because they are part of the data I have to store them with all the other bytes, not strip them out.

I was hoping bcp would be smart enough to ignore the terminators if I told it that there were none and that the file was binary with all records being the same length.

Mike

LongDriver Old Hand Points: 373 More actions · Answer 3

I finally got it to work. I tried it using text format but got the same syntax error as before. I then tried it using my original method but with a table named Test containing a field named Alink and it worked fine. I believe the problem was the two-word field name. It appears that bcp expects it to be a single word.

Thanks again for the assistance, Lynn. Even though it didn't quite work it narrowed the problem down a little more so I could eventually get it running.

Mike