Post reply

String or binary data would be truncated

  • May 30, 2009 at 9:09 am

    #132388

    Hi

    I have a proc that has been running for ages on a sql 2000 machine and we have recently upgraded it to 2005, when I run it I get the :

    Msg 8152, Level 16, State 2, Procedure sp_proc, Line 22

    String or binary data would be truncated.

    The weird thing is I only get it logging in with windows authentication, when I log in as sa it runs fine???

    any ideas?

    Thanks.

  • May 30, 2009 at 10:14 am

    #1002405

    The error usually raises when you try to INSERT/UPDATE too large string or binary data into a table.

    Please post the DDL of your procedure and the tables you UPDATE/INSERT inside your procedure.

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • May 30, 2009 at 12:37 pm

    #1002412

    Thanks for the reply, I have checked the data getting posted (in fact it returns no rows at all) but it is all ok, the issue seems to be because of the different privileges that sa has and the windows login has, and wondered if anyone had seen anything like it before.

  • May 30, 2009 at 1:50 pm

    #1002417

    I have seen something similar before - where using a login with sysadmin privileges bypasses certain checks and error messages.

    This is just one of the many reason why it is not recommended that you use the 'sa' account for application access.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • May 31, 2009 at 3:41 pm

    #1002523

    Found the issue in case anyone comes across it again.

    There was a default set for a UDF that used the function suser_name(), apparently in 2000 this returns null, but in 2005 it returns the full windows name 'DOMAIN/user_name' which was going over the limit of the UDF length.

    I have gone through and set a new default and all works ok.

    Thanks

  • June 1, 2009 at 7:48 am

    #1002766

    just curious how to reset the new default for udf length.

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

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