Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Agent » Msg 8152, Sev 16: String or binary data would...

22 posts, Page 3 of 3

«««1 2 3

Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]

Rate Topic

Display Mode

Topic Options

Author

Message

Roger Lin-302583

Posted Friday, January 15, 2010 12:06 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 15, 2013 9:18 AM
Points: 14, Visits: 25

If you don't care the data to be truncated, just turn the ANSI_WARINGS OFF, the data will be truncated to fit the row and no error no fuss everything will work fine. Here is what you can see the effect:

CREATE TABLE #tst (t char(2))
insert #tst values ('abc')

Execute that you get the following error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Then run this:
set ansi_warnings off
insert #tst values ('abc')

You will get this:
(1 row(s) affected)

Post #848497

peter.roothans

Posted Thursday, January 21, 2010 9:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 8:22 AM
Points: 4, Visits: 47

Hi,

I just had the same issue and it was caused by a trigger that stores all updates in an audit table. The audit table stores some additional information as SUSER_SNAME and APP_NAME ...

After adding substring function the problem was solved
SUBSTRING(APP_NAME(), 1, 50)

APP_NAME() in ssms is about 47 characters but when you execute via a job it returns SQL AGENT .... + a guid ... which is longer than 50 chars.

Kind regards
Peter.

Post #851388

« Prev Topic | Next Topic »

22 posts, Page 3 of 3

«««1 2 3

Permissions