Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

binary or string data would be truncated => error message enhancments

if you have that error (in production), it's not obvious to see which column or row this error comes from, and how to locate it exactly.
it's even more complicated if it's coming from a trigger, for example, and not the original insert/update.

I have seen that oracle meanwhile gives clear information which column the error is raising, which actual data length was passed, and the column's max size.

1,156 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

100 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Raj Yendluri commented  ·   ·  Flag as inappropriate

    CREATE TABLE small(tooshort varchar(3))
    INSERT small (tooshort) VALUES ('too long')

    I'm Looking for static code analysis to identify this issues (binary or string data would be truncated ) in the whole database procedures, I didn't find any tools which implemented this rule.

    Is it possible to throw these errors in compile time, by checking the source and destination column lengths in update/insert queries?

    Thanks
    Raj

  • Erland Sommarskog commented  ·   ·  Flag as inappropriate

    There is light in the end of the tunnel! I run this on SQL 2019 CTP 2.0:

    DBCC TRACEON(460)
    CREATE TABLE small(tooshort varchar(3))
    INSERT small (tooshort) VALUES ('too long')

    This is the output:

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Msg 2628, Level 16, State 1, Line 25
    String or binary data would be truncated in table 'tempdb.dbo.small', column 'tooshort'. Truncated value: 'too'.

    How about that!

  • Ricardo Russi commented  ·   ·  Flag as inappropriate

    Just this week i spent hours to detect what column originates truncation of data in some stored procedure. Will be awesome had information about the column that originates problem of that kind. Thanks in advance.

  • Soar commented  ·   ·  Flag as inappropriate

    pls fix this problem,the sql server given message is no help for solove the error.

  • Anonymous commented  ·   ·  Flag as inappropriate

    20 years later, nobody uses columns any more... datatypes.... what are those? I use quantum dataflops in a mrfusion reactor...

    Seriously though. The fact that this is being worked on is fantastic

  • DWalker07 commented  ·   ·  Flag as inappropriate

    Yay! I opened the original item (in Connect) many, many years ago. Glad to see this is getting some traction. Thank you.

  • JoeD commented  ·   ·  Flag as inappropriate

    Yay, it's being planned! Can't wait for SQL Server 2023 to get it!

  • Chris Bailiss commented  ·   ·  Flag as inappropriate

    Still makes me laugh/cry that this hasn't been resolved given the length of time SQL Server has been around. Think what all those DBA's and Devs could have done in that time had this been resolved ten or twenty years ago!

  • Manjuke Fernando commented  ·   ·  Flag as inappropriate

    It’s good that if the table,column and the data which is causing can be hinted. I guess it’s bit complex that the actual validation isn’t possible during query parsing in case we are inserting multiple rows. But this feature is a must since as Engineers it leaves clueless for us as well.

  • bob kennelly commented  ·   ·  Flag as inappropriate

    yes this is a must feature as right now i have to generate hundreds of TryConvert queries one every field in advance of doing a data migration in order to know specifically which field is causing the current "generic" error message, many thanks in advance!

  • Chris commented  ·   ·  Flag as inappropriate

    So as-of July, 2018, at least 1080 users were frustrated enough with this issue to search & find this topic, sign-up and vote as something that should be fixed. Assuming that they were able to find their way to the new Azure voice feedback site...

    The number of hours wasted by the community diagnosing this is ridiculous compared to both the time required to alleviate the problem and the MS development time for other non-essential features.

  • DWalker07 commented  ·   ·  Flag as inappropriate

    Small point.. The error message actually says "String or binary data would be truncated". The headline has the phrases backwards.

← Previous 1 3 4 5

Feedback and Knowledge Base