469,106 Members | 2,225 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

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
Tom
Jul 20 '05 #1
2 5776
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" <to**********@rocketmail.com> wrote in message
news:f1*************************@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

Jul 20 '05 #2
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" <to**********@rocketmail.com> wrote in message
news:f1*************************@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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Chris | last post: by
9 posts views Thread by Tony Lee | last post: by
1 post views Thread by djozy | last post: by
reply views Thread by NicK chlam via DotNetMonster.com | last post: by
3 posts views Thread by bbernieb | last post: by
5 posts views Thread by DC Gringo | last post: by
reply views Thread by Winder | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.