October 19, 2011 at 7:45 pm
Hey all,
Sometimes, when running TableDiff to compare two tables, some of the insert statements generated to sync the tables error out with the error:
[font="Courier New"]String or binary data would be truncated.
The statement has been terminated.[/font]
This is confusing to me, because if they are too big for a field in one table, they should be too big for the other.
Does anyone know a fast way to troubleshoot this? SQL doesn't identify the field that is causing the issue. Is there a fast way to tell this? I'd appreciate any help anyone can give.
October 19, 2011 at 11:41 pm
1) sp_help <source/target table name>
2) Open Excel
3) Copy the source table structure and paste to Excel Sheet1
4) Copy the target table structure and paste to Excel Sheet2
5) Click on Sheet1 and Sheet2 quickly. The difference will stand out.
October 20, 2011 at 12:39 am
The two table definitions could be different in any column(s) interms of the storage .
thanks
sarat 🙂
Curious about SQL
October 20, 2011 at 1:29 pm
Wow, that sp_help procedure is great. Thanks for letting me know. I had been copying the column info out of a view before, haha.:-P
I did match up the columns in Excel, and couldn't find the problematic field.
The weird thing about the whole situation is that the data is entered into the databases via a front-end program at one of two locations (and the data is synced between the two locations).
Unless there are some small differences between the two corresponding tables in the databases (quite unlikely for the tables concerned), there should be no way for a row to be present in one table that can't be inserted into another. I'm just wondering if it's some issue with escape characters or something (maybe a single quote somewhere), but not from what I have found so far. Anyway, thanks again for the help.
October 20, 2011 at 1:34 pm
Please go to each table and generate a CREATE script for each one and place the script here. Please also place the script for the compare/insert script.
Thanks,
Jared
Jared
CE - Microsoft
October 20, 2011 at 3:18 pm
This has to be one of the most irritating things about sql. Why can't the error contain the column name? The engine obviously knows which column. GGGGRRRRRR!!!!!!!!! There is connect item to have MS change the message to include the column name. I will see if I can find it so you can all vote on it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 24, 2011 at 7:38 pm
Hey all,
I realize this is an old thread, but a colleague helped me find the solution, and I wanted to post about it here since it describes a frustrating issue with TableDiff.
So, what turns out was causing the issue was that there was a row that was present in one database but not the other that had a field in it that was 1 character long and nullable, and that field contained a null value for this particular row.
Well, when TableDiff generated an insert query to put this row into the other database, it specified the value of this field as 'null'! As a result, SQL said, "The string 'null' doesn't fit into this 1-character field, so I'm going to throw a truncation error".
This issue can be solved by setting the field with the null value equal to a blank string, and then running TableDiff.
Thus, my advice if you're having problems like this is to look for nvarchar fields of length less than 5 characters that are nullable. I hope this helps someone.
December 25, 2011 at 3:08 am
Glad you are able to resolve it. Thanks for sharing the solution here.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply