locked
SQL AGENT JOB FAILURE--[SQLSTATE 22001] (Error 8152) RRS feed

  • Question

  • Hi all ,

    This sql agent job has four steps and its run every 5 mins it is running successfully but it failed once at step 1 throwing the error listed below.Recently this job and db's have been migrated to the new server which is 2012 from 2005 sql server.

    Message
    Executed as user: NT SERVICE\SQLSERVERAGENT. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621)  String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

    Did anybody faced the same issue before.

    Tuesday, February 19, 2013 1:49 PM

Answers

  • pls see the e.g .. ANSI_WARNING suppress the error. 

    CREATE TABLE t(test VARCHAR(10),d VARCHAR(5))
    
    CREATE PROC proc_t 
    AS 
    begin
    
    SET ANSI_WARNINGS  OFF
    
    INSERT INTO t VALUES('My name is victor and i am a good guy','d')
    
    SET ANSI_WARNINGS  ON
    
    END
    
    EXEC proc_t

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Tuesday, February 19, 2013 2:10 PM
    • Marked as answer by chinna738 Friday, February 22, 2013 10:10 AM
    Tuesday, February 19, 2013 2:07 PM

All replies

  • Thats because your script trying to insert into a column thats size is lesser than the data..

    CREATE TABLE #t(test VARCHAR(10))
    INSERT INTO #t VALUES('dfgdfgdfgdfgdfgdfgdfgdfgdfg')

     you can suppress it using following set cmd.. see the eg.

    SET ANSI_WARNINGS  OFF 
    INSERT INTO #t VALUES('dfgdfgdfgdfgdfgdfgdfgdfgdfg') 
    SET ANSI_WARNINGS  ON
    
    SELECT * FROM #t
    But to find out which column is causing this.. it is one of the hardest one..

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    • Edited by SimpleSQL Tuesday, February 19, 2013 2:02 PM
    Tuesday, February 19, 2013 1:52 PM
  • Thank you so much for the reply @v.vt,

    How can I set the ANSI_WARNINGS OFF ,shall I modify the stored proc 

    Why did you set the ANSI_WARNINGS ON again ??

    please do let me know Thanks :-)

    Tuesday, February 19, 2013 2:02 PM
  • pls see the e.g .. ANSI_WARNING suppress the error. 

    CREATE TABLE t(test VARCHAR(10),d VARCHAR(5))
    
    CREATE PROC proc_t 
    AS 
    begin
    
    SET ANSI_WARNINGS  OFF
    
    INSERT INTO t VALUES('My name is victor and i am a good guy','d')
    
    SET ANSI_WARNINGS  ON
    
    END
    
    EXEC proc_t

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Tuesday, February 19, 2013 2:10 PM
    • Marked as answer by chinna738 Friday, February 22, 2013 10:10 AM
    Tuesday, February 19, 2013 2:07 PM