Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    16

    Unanswered: SqlServer 2005 "String or binary data would be truncated" when data is OK

    When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.

    The scenario is as follows:

    Create table:
    create table test3 (
    name varchar (18) ,
    tbname varchar (18)
    )

    Create and populate table:
    create table maxtable (
    tablename varchar (18) not null,
    [...]
    )

    Try to insert into test3:
    insert into test3 (name, tbname)
    select i.name, o.name
    from dbo.sysindexes i, sysobjects o, maxtable m
    where i.indid > 0 and i.indid < 255
    and i.id = o.id and i.indid = 1
    and o.name = lower(m.tablename)

    And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.

    The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Try this, just for yuks and grins:
    Code:
    select max(len(i.name)), max(len(o.name))
    from dbo.sysindexes i, sysobjects o, maxtable m
    where i.indid > 0 and i.indid < 255
    and i.id = o.id and i.indid = 1
    and o.name = lower(m.tablename)
    Let us know the results.

  3. #3
    Join Date
    Mar 2005
    Posts
    16
    The max length is 18.

  4. #4
    Join Date
    Mar 2005
    Posts
    16
    Actually, the index name is max 18, the table name is max 16, but I get the error for either one when tried separately.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by karenc
    The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
    What matters is the datatype, not the actual length of the data. SQL Server has no way of knowing that every time you run this code you are only going to filter values less than 18 characters in length. It is letting you know that the datatypes are incompatible. Use CAST or CONVERT to explicitly limit the length of your input values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2005
    Posts
    16
    Yes, I just tried this with "convert", and that works, and I understand your explanation, but it seems wrong to me that SqlServer 2005 would perform differently than all prior versions of SqlServer in this situation. Do you think it's a bug?

  7. #7
    Join Date
    Mar 2005
    Posts
    16
    Or are the lengths of sysobjects and sysindexes "name" longer in 2005 than they are in 2000?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •