显示标签为“image”的博文。显示所有博文
显示标签为“image”的博文。显示所有博文

2012年3月27日星期二

BCP with image, ntext datatype

Hi, I have a table with ntext and image datatype. I did BCP out succesfully but when I try to to BCP in, I am getting error.
----
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
------
I read somewhere that I can use BULK INSERT with a format file.
Can someone suggest how to BCP in siccesfully or How does the format file looks like for this kind of task?. I am using SQL Server 2000.
---
Here is my table structure..
----
CREATE TABLE [dbo].[NOTIFY_TEMPLATE] (
[ID] [numeric](28, 0) NOT NULL ,
[SENDER] [varchar] (128) NOT NULL ,
[SUBJECT] [varchar] (512) NOT NULL ,
[BODY] [ntext] NOT NULL ,
[PRIORITY] [numeric](28, 0) NULL DEFAULT (2),
[ENABLED] [numeric](28, 0) NULL DEFAULT (1),
[LANGID] [numeric](28, 0) NOT NULL DEFAULT (0),
[NOTIFY_TYPE] [numeric](28, 0) NULL DEFAULT (0),
[REQUEST_TYPE] [numeric](28, 0) NULL ,
[CUSTOMIZED] [numeric](28, 0) NOT NULL DEFAULT (0)
) ON [DATA1] TEXTIMAGE_ON [DATA1]in order to perform a successful bcp in/bulk insert (same thing, essentially) you need to have a staging table which will hold only 1 record that'll contain the file you're bcping/bulk inserting. the format file will have to have a field width that will correspond to the file size in bytes.|||Hi, Thanks for reply..
Here is my situation..
With a fmt file, I am able to load the data succesfully, but, the data is going as garbage and some random numbers...
My format file looks like this..Body column has ntext datatype.
I gave the table DDL below..
----
8.0
10
1 SQLNUMERIC 0 13 "" 1 ID ""
2 SQLCHAR 0 128 "" 2 SENDER SQL_Latin1_General_CP1_CS_AS
3 SQLCHAR 0 512 "" 3 SUBJECT SQL_Latin1_General_CP1_CS_AS
4 SQLNTEXT 0 40000 "" 4 BODY SQL_Latin1_General_CP1_CI_AS
5 SQLNUMERIC 0 13 "" 5 PRIORITY ""
6 SQLNUMERIC 0 13 "" 6 ENABLED ""
7 SQLNUMERIC 0 13 "" 7 LANGID ""
8 SQLNUMERIC 0 13 "" 8 NOTIFY_TYPE ""
9 SQLNUMERIC 0 13 "" 9 REQUEST_TYPE ""
10 SQLNUMERIC 0 13 "" 10 CUSTOMIZED ""|||sqlntext column should have the following syntax:

4 SQLNTEXT 0 <file_size_in_bytes> "" 4 BODY SQL_Latin1_General_CP1_CI_AS|||Hi, Thank you for the reply.
How do I get the <file_size_in_bytes> parameter?
Because, there are multiple records in the DB, I gave an approximate size of 40000. But, still it fails..
Also, I see wrong data in almost every column.|||see my previous post about the staging table and 1 document per process|||The problem that I see is that the NTEXT column doesn't have well defined delimiters. Because of that, there isn't any clear way to find the end of it, which implies that there isn't a clear way to find the beginning of the "priority" column after the NTEXT column, other than to know the exact length of the NTEXT column.

My first suggestion would be to limit the data file to just one row and move the NTEXT column to the end of the row. Those two (?) changes would allow the NTEXT column to be treated as though it had a variable length (which would be set by the end-of-file condition). The only other way that I know to import this file using BCP would be to know the exact length (to the byte) of the NTEXT column, and specify that length in the FMT file.

-PatP|||Here is my situation.
----
I already have the data in SQL Server 2000 DB.
I want to export that data using BCP or DTS into flat file or files.
And import the data back into an empty schema using BCP or DTS, any commandline utility...
So, that I can create the empty schema first, import the data, then do some post processing, everything need to be run with commandline...
That is the reason I am exploring BCP, I read somewhere that we can use DTS also for this purpose. Can you elaborate more on this if you have any idea or experience in this..
Thanks a lot|||In a PM, Kishore said:Hi Pat, I see your reply to my message BCP...
This table has about 270 records. How would I know the length of one particular record of that column. That's the problem, it is UGLY to have to do this.

A short and sweet way to create an "offline" copy of a database is to do a SQL BACKUP (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp) followed by a SQL RESTORE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp) command. Both of these can be scripted using the OSQL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp) utility.

If that won't work for you, you can create a DTS package using either the DTS Wizard (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_wiz_9e5g.asp) or the DTS Designer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_wiz_9e5g.asp). Once you save the DTS package, it can be run via the SQL Agent (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp) or DTSRun (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_other_8reb.asp).

-PatP|||Hi, Thanks for the reply...
I am trying to create a package, when executed should be able to create the DB on any machine...
Loading data is a part of this package run.
Here is my question...
Can I use DTS to create a package to export all tables in flat file format...
Use the cmd line utility to run that package pretty much on any machine to import that data back.
When I selected the DTS export utility to create a package, it is allowing me to select one table at a time. If it is the case, I may have to create 300 packages..:-(|||Originally posted by Pat Phelan
The problem that I see is that the NTEXT column doesn't have well defined delimiters. Because of that, there isn't any clear way to find the end of it, which implies that there isn't a clear way to find the beginning of the "priority" column after the NTEXT column, other than to know the exact length of the NTEXT column.

My first suggestion would be to limit the data file to just one row and move the NTEXT column to the end of the row. Those two (?) changes would allow the NTEXT column to be treated as though it had a variable length (which would be set by the end-of-file condition). The only other way that I know to import this file using BCP would be to know the exact length (to the byte) of the NTEXT column, and specify that length in the FMT file.

-PatP
it all sounds very scientific, but i already posted that answer. also, end-of-file is an end-of-file is an end-of-file. and you need to bcp in specifying only 1(one, uno, ein, odin, etc.) field to be imported into a staging table, knowing exactly what the size of the file is, in bytes (xp_getfiledetails 'full_path_and_full_file_name')
once you know the size, you need to write your format file for bcp operation into a...staging table... i use sp_oaxxx procedures to do that. once the format file is ready, i use bulk insert using the format file into the ...staging table... once bulk insert is completed, i writetext from staging table into the actual table using all other fields i need.|||Hi, I used -n or -N switch with bcp and it solved my problem..
Thats guys for your input. it was helpful and more knowledgeful.
-Kingno1|||Originally posted by kingno1
Hi, Thanks for the reply...
I am trying to create a package, when executed should be able to create the DB on any machine...
Loading data is a part of this package run.
Here is my question...
Can I use DTS to create a package to export all tables in flat file format...
Use the cmd line utility to run that package pretty much on any machine to import that data back.
When I selected the DTS export utility to create a package, it is allowing me to select one table at a time. If it is the case, I may have to create 300 packages..:-(

i don't think dts or bcp are your answers. if you have such datatypes as n/text and image you will be able to save those tables in files, but you'll never be able to import them back unless the size of the field of n/text or image datatype is exactly the same for all rows, and if you do not run into a situation where the contents of the field contain the sequence of field or row terminator.

backup and restore is your only viable solution, but you can make it pretier than just that by ensuring that all users from the restored image have corresponding logins on the target server, and rectify the sid mismatches via sp_change_users_login. i'd recommend to take the guts out of that procedure and do it yourself, because you will not be able to run it within a script against multiple mismatched users, unless you take it outside with xp_cmdshell 'osql ... -Q"exec master.dbo.sp_change_users_login ..." -n'|||Originally posted by kingno1
Hi, I used -n or -N switch with bcp and it solved my problem..
Thats guys for your input. it was helpful and more knowledgeful.
-Kingno1

hey, good for you ;)

2012年3月19日星期一

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 encountered
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

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 encountered
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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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 encountered
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/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.
>>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.
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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