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
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    100 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      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