Member Avatar for NextCom

Error message ”[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.” comes when you insert/update a larger value than the column is specified for. But is there a way to detect what column, or what value that is to large?

I’m thinking about splitting up the INSERT string, and checking the length of all the char/varchar columns that is specified schema, and doing a LEN on the value to see if it’s larger than the length specified in the schema. But is there an easier way?

  • 2 Contributors
  • 4 Replies
  • 5K Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by NextCom
Member Avatar for sknake

No. The easiest way I have ever come up with handling this is to have all of the database information embedded in the calling application and check the data lengths before i do an update/insert. I have no idea why Microsoft did not include the column information but that is a very annoying error that can be hard to solve.

You can check the data in TSQL before your update/insert and call raiserror() if the data is too long but I personally find it easier to handle in code.

Member Avatar for NextCom

Damit, then I have to try to make the matching in the schema with the split, LEN and match, just to do the debugging a bit easier.

If it was possible to get the exact column, I would like to build in an automatic ALTER on the table, which extends the length of the column. But I don't have the guts to start with that when I’m not 100% sure it's the right column.

Member Avatar for sknake

That just sounds like a bad idea... You don't want to automatically start making schema alterations like that. There is some overhead and additional development time required to catch those bad inserts before they happen but I think that is about as far as you should go. There is an option to turn off this behavior and automatically truncate the string and insert it anyways... but I think that is a bad idea too. You want to enforce data integrity and this undermines that practice.

Sorry I couldn't have a straight forward answer for you. Please mark this thread as solved if I have answered your question.

Member Avatar for NextCom

Solved, because i don't think there is any "good" solution for the problem.

I was thinking of making the automatical extension of schema based on user rights, because our customers have diffrent needs.
When an administrator recives this error message, then he just clicks one button and it automaticly extends the lenght. Without the need of contacting us.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.