Hi!

A client is trying to export the table into another database but receives the following error:
"The value is not consistent with the data type or length of the
column." So far I can't replicate the issue on any of my test
environments. (this is not the problem tho... =))

However, when you do try to copy the row and paste it at the end of the SAME table. you the the same error:
"The value you enetered is not consistent with the data type or
length of the column."

And I find this bizarre because 1) it IS already in the database with no problems and 2) the field is of type Text - which should accommodate very long text values.

I also found this same problem occuring in fields of type VarChar.

Also, in trying to solve the problem, I found that the problem is caused by a CHR(13) [vbCr] and a CHR(10) [vbLf] at the end of the text, and deleting them allows you to paste the rebellious row (but doesn't make the export problem go away). However, I also found a few rows that end with those characters but does NOT generate the error when copied and pasted.

MS SQL Server database bug? What can I do to solve it?

A sample of the table is available as a database backup from http://users.tpg.com.au/adslnl7k/gw/test.zip

Thanks !!


Wind is your friend
Matt

__________________
Wind is your friend
Matt

There are situations where you can load data in differen methods, for example bcp vs insert. The data will fail if you use an insert but the bcp has looser acceptance logic and thus will accept the data. When I have come across this it was always because of a strange character. Display the line you know that fails on it's acsii value for every character in the line. Or try to insert the line into itself but field at a time to identify what field is the problem. The error your getting is not the correct error. Its not a length issue its likely a strange character issue. The data was likely brought in with a etl process, bcp, or some non insert type process that circumvented the logic in place the way your currently adding the same data in. Find the strange character, change it to a blank or delete it and you should be able to do what you want to do. After you identify what character that is write some code to look for the same value in other places.

Hope this helps