Post reply

String or binary data would be truncated.

  • April 19, 2005 at 9:58 am

    #66945

    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

  • April 19, 2005 at 12:24 pm

    #553533

    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.

  • April 19, 2005 at 12:43 pm

    #553544

    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 type To data type Result
    int, smallint, or tinyint char *
      varchar *
      nchar E
      nvarchar E
    money, smallmoney, numeric, decimal, float, or real char E
      varchar E
      nchar E
      nvarchar E

     

     

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

  • April 20, 2005 at 3:38 am

    #553707

    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.

     

     

  • April 20, 2005 at 5:08 am

    #553728

    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