BCP Error - Oversized Column
Question
Tuesday, February 27, 2018 11:41 AM
Hi all,
I'm trying to copy data from an Azure SQL table to a SQL Server 2014 table but am getting the following error message
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Attempt to bulk-copy an oversized column to the SQL Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Text column data incomplete
I have created the destination table by scripting out the table definition of the source table. However when creating the destination table I get this warning:
Warning: The maximum length of the row exceeds the permissible limit of 8060 bytes. For some combination of large values, the insert/update operation will fail.
The command I'm using is:
bcp MyDatabase.dbo.MyTable in "C:\temp\mytable.bcp" -n -T -SMyServer
The command copies 4.2 million records (out of 11 million) but then fails with the above error message.
When I tried the -c flag instead of -n it was even worse and failed after just 200,000 rows.
I would have thought that with the table definition on the source and destination being identical then this should not have been a problem.
All replies (4)
Tuesday, February 27, 2018 11:58 AM
Hi Ranvir,
This might be due to the invalid data on the table .. something like a special characters will be there in you data , try to find out that and replace it with hexadecimal value .. You can use notepad++ to identify those kind of things
if it is new line character issue use the the below switch in your command
-r "0x0a"
bcp MyDatabase.dbo.MyTable in "C:\temp\mytable.bcp" -n -T -r "0x0a" -SMyServer
Tuesday, February 27, 2018 1:42 PM
Hi Liju,
Thanks for your reply, unfortunately the file is 23GB in size so can't be opened to view in Notepad++, is there anything else I could do?
If I export a different smaller table and look at that in Notepad++, could that also point to what the issue could be?
Tuesday, February 27, 2018 2:23 PM
Hi Ranvir,
Can you run the query in source table to check the special characters
most of the cases special characters will be
carriage return: char(13)
line feed: char(10)
below query is used for identify those records
SELECT * FROM some_table
WHERE CONTAINS(some_field, char(13)) OR CONTAINS(some_field, char(10)) or CONTAINS(some_field, char(10)+char(13))
Thanks
LJ
Tuesday, February 27, 2018 2:38 PM | 1 vote
Why did you use BCP to import data ..
is it possible to do this by import/export wizard ... I think if the instances are in same domain then it is possible.
otherwise you need a VPN connection between the domain.
one more way is there
if you have enough privileges in both instance then move the source table to a new database and take a compressed back up and restore it in destination server and move the table to your destination db ... this one is not recommended unless you are in emergency situation ...