BCP error 22001 - String data, right truncation

[Leslie]

I am using bcp to copy a table from one sql server to another. The bcp out
seems to work fine. However, when I attempt to bcp the data back in, I get
the following errror:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

The source SQL Server is: Microsoft SQL Server 2000 - 8.00.760

The destination SQL Server is: Microsoft SQL Server 2000 - 8.00.871

What do I need to do to resolve this error?

Thanks,

Leslie

[Brian Lawton]

First be sure the table schema on the target server matches the one on the
source server. After that, be sure the column delimiters in your data file
are such that any imbedded commas within the data are not causing a problem
when reading the data. As a possible solution, try using the Native Type
option (-n) when you export/import the data rather than saving it as text.
Beyond that, you'd need to post more information detailing exactly how you
are performing the bcp.

--
--Brian
(Please reply to the newsgroups only.)

"Leslie" <ma...@newsgroup.nospam> wrote in message
news:21F78426-D27C-474C...@microsoft.com...

[Leslie]

Brian,

Thanks for the input. The schema for the tables match. I think the problem
was imbedded CRLFs. The text data is entered via the web and I think people
are pressing enter at the end of a line and then starting a new line. Would
this sound like the kind of thing that would cause the problem?

Once I switched to native mode (-n) the problem went away.

Thanks for your help.

[Brian Lawton]

The imbedded CRLF's would definitely cause the problem. Generally I have my
application developers strip those types of garbage characters out before
allowing it to be saved to the database. The characters usually provide
them as much of a problem when they later try to redisplay the data as when
you try to use it directly in the database.

Glad to hear it's working now!

--
--Brian
(Please reply to the newsgroups only.)

"Leslie" <ma...@newsgroup.nospam> wrote in message

news:66ED84D5-A469-4E29...@microsoft.com...

[Robert Klemme]

Another option is to use DTS - which is IMHO much more convenient for
transfer between two SQL Server instances. That will avoide some of the
obstacles you encounter while transforming to CSV and back.

Kind regards

robert

[Leslie]

A good option, thanks.

[Robert Klemme]

Irish wrote:
> Which DTS connector do you recommend to import a BCP'd file? I
> usually use BCP to pop tables out of one server into another one at a
> different site. As BCP is not as user friendly, which DTS connector
> best simulates the BCP.

If you have direct connection between the two databases then a direct
transfer is the best option. Otherwise a backup might work, too. If you
have to do this often / on a regular basis then I'd look into replication.

robert

[Irish]

Robert:

There is not a direct connection between the two databases. Usually the
tables that I need to add into the database are for data repair of databases
and the addition of new rows for merging multiple database, etc. Therefore
rreplication is not going to work.

Is there a connector that can simulate the BCP format?

Irish

[Roger Quispe Del Castillo]

the real problem is as shown in the message is truncated, it means that the
value you are trying to insert is greater than the length of the field in
the table and that happens because they are not finding the bcp separator
rows, in contrast to all the rows it is taking as a single row then the
solution would be to use something like this, I hope might help.

bcp Intratim.dbo.usuariosnofiscalizables in
\\cst02t12617\PerFiscalizable\RRHH1.LOG -c -r~ -Uusrbdintratim -Pintranet -t,
-SLIMBD1A\intranet

-r~ = separator

visit my website: www.espelfactorysolutions.com

url:http://www.ureader.com/msg/1162375.aspx