String or binary data would be truncated.

  • Hi,

    Server: Msg 8152, Level 16, State 9, Line 78

    String or binary data would be truncated.

    The statement has been terminated.

    This error happens when I try to insert a nvarchar255 value into a bigint column:

    I've tried a cast statement :-

    CAST (field1 AS BIGINT) URI,                  (where field1 is nvarchar255)

    But the problem persists. Any help to overcome this problem would be greatly appreciated.

    Thanks

  • I don't get that error. I get an overflow error if the number in the field1 is too big.

    Check other columns in the table. Check defaults, too. I just had the case of the default being too long to fit the column and every insert without the column generated the error.

  • Expected behaviour. Quoting from BOL (CAST and CONVERT):

    When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.

    From data typeTo data typeResult
    int, smallint, or tinyintchar*
     varchar*
     ncharE
     nvarcharE
    money, smallmoney, numeric, decimal, float, or realcharE
     varcharE
     ncharE
     nvarcharE

     

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Sorry guys. I was barking up the wrong tree.

    It was complaining about another field. Because I was creating a table "on the fly" one of the values I was using was a 3 character transaction code.

    Subsequently, when I populate the same table I'm using 4 character transaction codes.

    I'm new to SQL Server so it's been a painful lesson for me. I didn't realise when you create a table "on the fly" whatever data is used its characteristics are taken on by the table.

    Thanks for your help anyway.

     

     

  • Exactly how are you creating the table 'on the fly' ...? It might help to understand what really happened (and also help to avoid it next time)

    /Kenneth

Viewing 5 posts - 1 through 4 (of 4 total)

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