 |
|
|
|
Next: Creating a diagram with existing tables
|
| Author |
Message |
External
Since: Aug 08, 2005
Posts: 3
|
(Msg. 1) Posted: Mon Aug 08, 2005 11:39 am
Post subject: BCP error 22001 - String data, right truncation
Archived from groups: microsoft>public>sqlserver>tools (more info?)
|
|
|
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 |
|
| Back to top |
|
 | |
External
Since: Aug 08, 2005
Posts: 12
|
(Msg. 2) Posted: Mon Aug 08, 2005 3:07 pm
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
>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 |
|
| Back to top |
|
| |
External
Since: Aug 08, 2005
Posts: 3
|
(Msg. 3) Posted: Mon Aug 08, 2005 3:07 pm
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote:
> 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" wrote in message
>
> >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
>
>
> >> Stay informed about: BCP error 22001 - String data, right truncation |
|
| Back to top |
|
| |
External
Since: Aug 08, 2005
Posts: 12
|
(Msg. 4) Posted: Mon Aug 08, 2005 3:50 pm
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
> 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" wrote:
>
>> 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" wrote in message
>>
>> >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
>>
>>
>> >> Stay informed about: BCP error 22001 - String data, right truncation |
|
| Back to top |
|
| |
External
Since: Oct 01, 2003
Posts: 119
|
(Msg. 5) Posted: Tue Aug 09, 2005 6:55 am
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Aug 08, 2005
Posts: 3
|
(Msg. 6) Posted: Tue Aug 09, 2005 7:01 am
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
A good option, thanks.
"Robert Klemme" wrote:
>
> 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
>
> >> Stay informed about: BCP error 22001 - String data, right truncation |
|
| Back to top |
|
| |
External
Since: Sep 09, 2005
Posts: 4
|
(Msg. 7) Posted: Fri Sep 09, 2005 3:26 pm
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Sep 09, 2005
Posts: 4
|
(Msg. 8) Posted: Fri Sep 09, 2005 3:29 pm
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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.
Thanks!
Irish
"Robert Klemme" wrote:
>
> 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
>
> >> Stay informed about: BCP error 22001 - String data, right truncation |
|
| Back to top |
|
| |
External
Since: Oct 01, 2003
Posts: 119
|
(Msg. 9) Posted: Mon Sep 12, 2005 4:55 am
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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 >> Stay informed about: BCP error 22001 - String data, right truncation |
|
| Back to top |
|
| |
External
Since: Sep 09, 2005
Posts: 4
|
(Msg. 10) Posted: Mon Sep 12, 2005 7:30 am
Post subject: Re: BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
"Robert Klemme" wrote:
> 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
>
> >> Stay informed about: BCP error 22001 - String data, right truncation |
|
| Back to top |
|
| |
|
Roger Quispe Del Castillo
|
External
Since: Nov 27, 2009
Posts: 1
|
(Msg. 11) Posted: Thu Nov 26, 2009 7:25 pm
Post subject: Re:BCP error 22001 - String data, right truncation [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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 >> Stay informed about: BCP error 22001 - String data, right truncation |
|
| Back to top |
|
| |
| Related Topics: | past multi-line string into text field using 2005 Manageme.. - Is there a way to paste in a multi-line string into a text field when the table is open in 2005 Management Studio? It only seems to take the 1st line. -- Thanks in advance, Les Caudle
Has anyone seen this error? -
Trying to Copy DB and getting Error - Hi, I'm trying to "copy" a DB w/i 2k5 and when the wizard goes to execute my request, i get an error "No such interfacess supported." No details are provided, just this message. I'm unsure what it means or how to resolve it. SQL...
SSMS Error - We have two production servers running SQL 2000 and that are set up as linked servers. My desktop has SQL 2005 and I am using MAnagement Studio to connect to the first server. While running a stored procedure on a 1st remote server that inserts data..
Taskpad Error - FYI I left a message a few months back about errors in the Taskpad in Enterprise Manager. No fix was found at that time but I can now report a fix. A similar error was seen executing a java script in a help file. My IT guy fixed that one by going int... |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
