String right truncation error

[jwdcfdeveloper]

Does anyone have any idea what this error code means in English: "ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated". I am having this problem with a couple of my databases. The Online Books did not tell me anything that I already did not know (the error code and simple definition).

 

[fluteplr]

You are trying to insert or update a field in a table with a string that is longer than it can hold. For example trying to place 'this is a long string' in a field defined as a char(10).

 

[jwdcfdeveloper]

The problem here is that no matter what is entered into the field, whether it's one character or 2000 characters, the field is truncating. In fact, the field's data type is text, and the information that is being entered is much smaller than the text data type's upper limits

 

[fluteplr]

Got me. It would have been nice if you had supplied that additional information in the first place. Other than telling you to recheck your assumptions, i.e. the data really is going into a text field, and possibly running a sql trace so you can see what the odbc statement looks like on the server side. I haven't got a clue.

 

[tlbroadbent]

Are you certain that you've identified the column that is causing the error?

FYI: If data truncation is okay with you and you don't want to see this error, then turn off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[aalmeida]

Are you sure you have identifyed the correct place on your coude that is causing you the error message to pop up?
I have seen that this error several times on situations where a variable in the code was defined with a data type with smaller size that the actual data type, the most comom place I have seen this was caused by a insert or update with cast or convert datetime to char() or isnull(Exp1, Exp2) function where is very easy to mismatch datatype sizes. More on mid process and manipulation than comparing the initial and final datatypes AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"