Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001] Expand / Collapse
Author
Message
Posted Friday, January 15, 2010 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 7:06 PM
Points: 14, Visits: 34
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
Posted Thursday, January 21, 2010 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:13 AM
Points: 8, Visits: 93
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 »

Add to briefcase «««123

Permissions Expand / Collapse