bcp right truncation: how to ignore

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(mudassir.latif@.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, 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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.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