is there a limitation in BCP utility?

[shanghai2004]

Dear Expert,

I use BCP to export data from tables in a database, then import the data files to the same tables in another database.
I had trouble to import data to a table. The table definition is:

CREATE TABLE [dbo].[TWORKFLOWMAPXMLDEF] (
[ORGANIZATIONID] [int] NOT NULL ,
[UNIQUEID] [int] IDENTITY (1, 1) NOT NULL ,
[WORKFLOWMAPID] [int] NULL CONSTRAINT [TWORKFLOWMAPXMLDEF_WORKFLOWMAPID_DF] DEFAULT (0),
[SORTORDER] [int] NULL CONSTRAINT [TWORKFLOWMAPXMLDEF_SORTORDER_DF] DEFAULT (0),
[XMLTEXT] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [TWORKFLOWMAPXMLDEF_XMLTEXT_DF] DEFAULT (' '),
CONSTRAINT [TWORKFLOWMAPXMLDEF_PK] PRIMARY KEY NONCLUSTERED
(
[UNIQUEID]
) ON [PRIMARY]
) ON [PRIMARY]

The BCP commands are:
C:\>bcp tenrox.dbo.TWORKFLOWMAPXMLDEF out "c:\sama data\MSSQL\data files\tenrox\
export\_TWORKFLOWMAPXMLDEF.txt" -c -T -C ACP

Starting copy...

135 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 120

C:\>bcp citenrox.dbo.TWORKFLOWMAPXMLDEF in "c:\sama data\MSSQL\data files\tenrox
\export\_TWORKFLOWMAPXMLDEF.txt" -c -T -C RAW -E -h "CHECK_CONSTRAINTS"

Starting copy...
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
....
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range

9 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 140

Since the length of column XMLTEXT is 4000. I added options -b 1 and -a 32768 to command. It did not help.

I view the data file, it seems that if a row has
<!--TWFLOW diagram--> in column XMLTEXT, the next row will not be imported. Here is the first row in the data file.
1 139 24 0 <?xml version="1.0"?>
<!--TWFLOW diagram-->

The first row was improted to table but not the second row.
I am not familiar with XML, why the
<!--TWFLOW diagram--> is so special.

If I use SELECT (table from on db) then INSERT (to table in another db) it went successfully. But it has more issues need to be considered and not easy to make it for all tables. For example I need to select columns for each table and deal with the triggers.

I wish I can use BCP command for all tables (I created a VBS scrip to execute the BCP command). Help please!


Thanks

 

[yelworcm]

A couple of things.

Why is the code page changing? From ACP to RAW?

I don't see a field delimiter declared. If I remember rightly, the default delimiter is just ",". This character may appear in your XML string. Try hanging it to "|" or even "myOwnDelimiter". Or, you could use /n for native format.

The bigger problem would appear to be the row terminator, whcih by default is \n, which does appear in your XML. Try using /r"rowterminator", or again, use /n for native format.

 

[shanghai2004]

Thanks! I will follow your suggestions and do more test.

There are two languages, English and French in Char. fields. If I keep the same code page ACP, some French letters are not converted correctly. I use RAW in import (it means no convention from one code page to another occurs during the importing) both English and French characters are converted correctly.

Option -c means tab as the field delimiter and newline as the row terminator.

Thank you again!

 

[shanghai2004]

Thanks expert.
I use /r/n as row delimitor it solved my all problems in BCP import.