BCP into image columns

I'm trying to use the bcp executable to insert rows into a table
containing two guids and two image columns. I'm using an explicit
character string for the field delimiter, and another for the row
delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
command I get an error.
C:\>bcp tempdb..t_test in test.bcp -t "||^t^||" -r "||^r^||" -S
myserver -U uname -P pwd -c -N
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountere
d
in BCP data-file
0 rows copied.
I tried the same bcp command with a format file provided, and still
received that same errror. The format file is as follows.
8.0
4
1 SQLUNIQUEID 1 16 "||^t^||" 1
guid1 ""
2 SQLUNIQUEID 1 16 "||^t^||" 2
guid2 ""
3 SQLIMAGE 4 0 "||^t^||" 3
img1 ""
4 SQLIMAGE 4 0 "||^r^||" 4
img2 ""
Am I missing something here? Or are image columns just a pain to work
with?
--ian(ian_jacobsen@.hotmail.com) writes:
> I'm trying to use the bcp executable to insert rows into a table
> containing two guids and two image columns. I'm using an explicit
> character string for the field delimiter, and another for the row
> delimiter (not the usual "\t" and "\r\n"). When I do the following bcp
> command I get an error.
And how does the data in the file look like? Judging from your description
there is newlines in it. Is that correct?

> I tried the same bcp command with a format file provided, and still
> received that same errror. The format file is as follows.
> 8.0
> 4
> 1 SQLUNIQUEID 1 16 "||^t^||" 1 guid1 ""
> 2 SQLUNIQUEID 1 16 "||^t^||" 2 guid2 ""
> 3 SQLIMAGE 4 0 "||^t^||" 3 img1 ""
> 4 SQLIMAGE 4 0 "||^r^||" 4 img2 ""
That's a format file for a binary file. Your BCP command was for a
character file.

> Am I missing something here? Or are image columns just a pain to work
> with?
image are indeed difficult to deal with, but I have a feeling you have
not reached those problems yet. :-) SQL 2005 has varbinary(MAX) which
can fit as much data as image, but is a lot easier to deal with.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>From your reply I'm guessing that my approach is wrong. The data that
I am trying to insert into the image column is an xml file that has
been compressed. Since it is very costly to store xml in its native
state, I would like to compress this data and store it in the database.
I'm exporting a dataset to xml, compressing that data, and then I
append this data onto my bcp file containing the two guids. In fact I
have two datasets that I'm trying to append. Is there a better way to
go about compression? Or a better way to format my bcp file?

>And how does the data in the file look like?
I'd rather not post the contents of my bcp file here (since its filled
with binary data and would be a mess), but here is a sample of the
layout with the binary cut down.
035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-5a0bbfcb=
a033||^t^||PK
Ti4||^t^||PK Ti4||^r^||

>Judging from your description there is newlines in it. Is that correct?
I'm presuming that a binary file can contain any characters, including
newlines.
--ian|||(ian_jacobsen@.hotmail.com) writes:
> From your reply I'm guessing that my approach is wrong. The data that
> I am trying to insert into the image column is an xml file that has
> been compressed. Since it is very costly to store xml in its native
> state, I would like to compress this data and store it in the database.
Undoubtedly, SQL 2005 should be very appealing to you, as it has a
built-in xml data type.

> I'd rather not post the contents of my bcp file here (since its filled
> with binary data and would be a mess), but here is a sample of the
> layout with the binary cut down.
Ah, that clarifies things quite a bit. This format file could work:
8.0
4
1 SQLCHAR 0 0 "||^t^||" 1 guid1 ""
2 SQLCHAR 0 0 "||^t^||" 2 guid2 ""
3 SQLIMAGE 0 0 "||^t^||" 3 img1 ""
4 SQLIMAGE 0 0 "||^r^||" 4 img2 ""
Since the GUIDs appears as text in the data file, the correct data
type for these is SQLCHAR. I've also set the prefix length to 0,
as your file does not seem to include length prefixes. (Although this
could be a better choice for the image columns, rather than using
a separator which in case of bad luck could appear in the data.

> 035caafb-db11-4c9a-8a60-acbcb434d5cf||^t^||177c5700-36b0-4a0b-b0b6-
5a0bbfcba033||^t^||PK
> Ti4||^t^||PK Ti4||^r^||
And the next GUID appears directly after ||, that is no newline?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||When using the format file that you provided, this fixed the problem I
was having.
Thanks
--IAN|||(ian_jacobsen@.hotmail.com) writes:
> When using the format file that you provided, this fixed the problem I
> was having.
Ah! I am glad to hear that! I was afraid that it would not be that simple,
but we would hit some other snag.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx