String or binary data would be truncated

  • Comments posted to this topic are about the item String or binary data would be truncated

  • I am sorry Steve, but truncating data without the user knowing is an error.  It is frustrating that the source and target schema don't match, but it is still an error.  Either the source data is wrong (swapped columns by mistake, etc) or the target schema is wrong.  Either way: It should tell me that.  A simple substring in the source query can fix it, if you are willing to lose data.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Agree with you Japie, although I think there should be a function to override truncation on normal inserts. 
    If I understand Steve correct, we'd like something similar to SSIS, to choose what happens when truncation should/could occur, along the lines of:

    *fictional command below*
    INSERT WITH NoStopOnTruncate INTO myTable values('string that would be truncated if longer than len')

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • I'd be happy if an error message at least indicated the row number and column before throwing the rattle out of the pram.

  • Even just knowing which column, would be a huge help.

  • I think a table or column option would be a great solution for this - then its up to the developer to turn it on for the table or column if they want it to truncate varchar's and nvarchar's

    if you don't have the time to do it right, when will you have the time to do it over ?

  • one freaky thing though .. this issue is posted on "feedback.AZURE.com ..." - as if SQL Server was a sub-entity of the Azure cloud world (I sure as sh*t hope not!)  Anyhow, upvoted it also, thx Steve for keeping this issue alive.

  • If I remember correctly, MS Access has been providing this info years ago

    ...

    -- FORTRAN manual for Xerox Computers --

  • I agree with you Steve, this has to be addressed. I would suggest Microsoft handle character data differently altogether and treat varchar as a string the same way C# or Java or ObjectiveC etc do; at least as far as removing the length specification. That's a bigger thing to accomplish since it effects the storage engine and query optimization and indexing and on and on, but this is Microsoft, I've seen what they can do and it's impressive, this can be done too.

    -

  • Japie Botma - Thursday, June 28, 2018 2:15 AM

    I am sorry Steve, but truncating data without the user knowing is an error.  It is frustrating that the source and target schema don't match, but it is still an error.  Either the source data is wrong (swapped columns by mistake, etc) or the target schema is wrong.  Either way: It should tell me that.  A simple substring in the source query can fix it, if you are willing to lose data.

    You've missed the entire point of the piece. This isn't about eliminating the error or silently truncating data. This is about ensuring the developer or DBA can find the source of the error easily.

  • Henrico Bekker - Thursday, June 28, 2018 2:59 AM

    Agree with you Japie, although I think there should be a function to override truncation on normal inserts. 
    If I understand Steve correct, we'd like something similar to SSIS, to choose what happens when truncation should/could occur, along the lines of:

    *fictional command below*
    INSERT WITH NoStopOnTruncate INTO myTable values('string that would be truncated if longer than len')

    I haven't necessarily proposed a fix, and I'd be nervous about truncating. If anything, I'd truncate but kick out the data as an error value. We just need some way to understand where and what the error is.

  • Matthew Joughin - Thursday, June 28, 2018 4:53 AM

    I think a table or column option would be a great solution for this - then its up to the developer to turn it on for the table or column if they want it to truncate varchar's and nvarchar's

    It's not always a fundamental coding issue. This could be a data issue for a single data load.

  • riix - Thursday, June 28, 2018 6:19 AM

    one freaky thing though .. this issue is posted on "feedback.AZURE.com ..." - as if SQL Server was a sub-entity of the Azure cloud world (I sure as sh*t hope not!)  Anyhow, upvoted it also, thx Steve for keeping this issue alive.

    This is because Azure is the subset of the MS area and that's where feedback is. There's not sqlserver.com site for feedback, so this is it.

  • riix - Thursday, June 28, 2018 6:19 AM

    one freaky thing though .. this issue is posted on "feedback.AZURE.com ..." - as if SQL Server was a sub-entity of the Azure cloud world (I sure as sh*t hope not!)  Anyhow, upvoted it also, thx Steve for keeping this issue alive.

    Actually, everything you see in the on-premise version of SQL Server is developed first in SQL Azure.  If you want to the latest and greatest, that is where you will find it before you can use it on-premise.

  • Japie Botma - Thursday, June 28, 2018 2:15 AM

    I am sorry Steve, but truncating data without the user knowing is an error.  It is frustrating that the source and target schema don't match, but it is still an error.  Either the source data is wrong (swapped columns by mistake, etc) or the target schema is wrong.  Either way: It should tell me that.  A simple substring in the source query can fix it, if you are willing to lose data.

    Hello Japie,

    Steve is not complaining about the error, but the vagueness of the error. The error doesn't specify the location of the offending column(s). In a SQL statement with many columns, it becomes a real chore to find out which column(s) are causing the error.

    Regards,
    Omar

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply