By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

bcp right truncation: how to ignore

P: n/a
Hi,

I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?

I couldn't find such an option in the documentation.

Any help is greatly appreciated.

Thanks,

Mudassir Latif

Mar 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(mu************@gmail.com) writes:
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?


Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.
--
Erland Sommarskog, SQL Server MVP, es****@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
Mar 16 '06 #2

P: n/a

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
(mu************@gmail.com) writes:
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?


Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.


Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).

Kind regards

robert

Mar 17 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.