BCP in of hex data

Can anyone please tell me if it is possible to bcp in a file with hex
data into SQL Server 2000?

I have a file that the characters appear as blocks when viewed in
notepad but appear as hex values in a hex editor. I have put just one
of these characters (hex 1A) into a file and tried to bcp it in to a
table with one column that is of type binary using a format file. I
can't get it to work.

Can anyone please help?

Thank you
TomHi

You don't give the BCP commands that you are using, but if you create the
format file by exporting the data then the same format file should work when
importing it.

create table mybinarydata ( col1 varbinary(4), col2 binary(4) )

INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 1234, 5678 )
INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 2345, 6789 )

bcp "test..mybinarydata" out mybinarydata.bcp -T -S MyServer

Format file if you take all defaults:

8.0
2
1 SQLBINARY 1 4 "" 1 col1
""
2 SQLBINARY 2 4 "" 2 col2
""

If you open the data file you should notice it is not readable.

If you use

bcp "test..mybinarydata" out mybinarydata.bcp -c -T -S MyServer

You will not be prompted for a format file and get the data file contains:

00001234 00005678
00002345 00006789

For using format files see books online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm

HTH

John

"Thomas Richards" <tom.richards@.rocketmail.com> wrote in message
news:f118866.0404140941.21eb0091@.posting.google.co m...
> Can anyone please tell me if it is possible to bcp in a file with hex
> data into SQL Server 2000?
> I have a file that the characters appear as blocks when viewed in
> notepad but appear as hex values in a hex editor. I have put just one
> of these characters (hex 1A) into a file and tried to bcp it in to a
> table with one column that is of type binary using a format file. I
> can't get it to work.
> Can anyone please help?
> Thank you
> Tom|||Hi

You don't give the BCP commands that you are using, but if you create the
format file by exporting the data then the same format file should work when
importing it.

create table mybinarydata ( col1 varbinary(4), col2 binary(4) )

INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 1234, 5678 )
INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 2345, 6789 )

bcp "test..mybinarydata" out mybinarydata.bcp -T -S MyServer

Format file if you take all defaults:

8.0
2
1 SQLBINARY 1 4 "" 1 col1
""
2 SQLBINARY 2 4 "" 2 col2
""

If you open the data file you should notice it is not readable.

If you use

bcp "test..mybinarydata" out mybinarydata.bcp -c -T -S MyServer

You will not be prompted for a format file and get the data file contains:

00001234 00005678
00002345 00006789

For using format files see books online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm

HTH

John

"Thomas Richards" <tom.richards@.rocketmail.com> wrote in message
news:f118866.0404140941.21eb0091@.posting.google.co m...
> Can anyone please tell me if it is possible to bcp in a file with hex
> data into SQL Server 2000?
> I have a file that the characters appear as blocks when viewed in
> notepad but appear as hex values in a hex editor. I have put just one
> of these characters (hex 1A) into a file and tried to bcp it in to a
> table with one column that is of type binary using a format file. I
> can't get it to work.
> Can anyone please help?
> Thank you
> Tom