Job fails but runs manually

  • hi,

    I schedule a job every morning, but it fails ...so I have to run it manually. When manually run, it always is successful.

    The error i get when i schedule it is :-

    String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

    Regards,

    SUSHANT

    Regards
    Sushant Kumar
    MCTS,MCP

  • More details please....

  • You probably have a conflict in a field size. Check to make sure if you are importing data in that your fields are large enough to accept them.

    Some more information would help though. Maybe providing more of the error log or the code of what your job is actually doing.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • The job code is :--

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    DECLARE @subject VARCHAR(250), @message VARCHAR(4000), @query VARCHAR(4000),

    @recipients VARCHAR(250)

    DECLARE @ITEMNMBR VARCHAR(31),@ITEMDESC CHAR(101), @PRCLEVEL VARCHAR(25),@UOFM VARCHAR(10),

    @UOMPRICE NUMERIC(19,5), @QTYBSUOM NUMERIC(19,5), @CALPRICE NUMERIC(19,5),

    @ACTUALPRICE NUMERIC(19,5)

    -- SELECT * INTO IV00108_05APR07_0700PM FROM IV00108

    -- Table to hold Price Changes

    DECLARE @KITPRICES TABLE

    (

    ITEMNMBR CHAR(31),

    ITEMDESC CHAR(101),

    PRCLEVEL CHAR(25),

    UOFM CHAR(10),

    QTYBSUOM NUMERIC(19,5),

    UOMPRICE NUMERIC(19,5),

    CALPRICE NUMERIC(19,5)

    )

    -- Get Old and New Prices

    INSERT INTO @KITPRICES

    (

    ITEMNMBR,

    ITEMDESC,

    PRCLEVEL,

    UOFM,

    QTYBSUOM,

    UOMPRICE,

    CALPRICE

    )

    SELECT

    IV.ITEMNMBR,

    IV.ITEMDESC,

    PL.PRCLEVEL,

    PL.UOFM,

    PL.QTYBSUOM,

    PL.UOMPRICE,

    dbo.rtw_fn_Kit_Price(IV.ITEMNMBR, PL.PRCLEVEL)

    FROM

    IV00101 IV

    JOIN IV00108 PL

    ON IV.ITEMNMBR = PL.ITEMNMBR

    WHERE

    IV.ITEMTYPE = 3 AND

    PL.PRCLEVEL = 'VG' AND -- Remove (PL.PRCLEVEL = 'C&C' OR ... C& C For the RMS Go Lve .

    -- Included in this run only

    --IV.ITEMNMBR NOT IN

    --(SELECT ITEMNMBR FROM IV00104 WHERE ITEMNMBR LIKE '%-CN%' AND CMPITUOM != 'CN')

    PL.UOMPRICE != dbo.rtw_fn_Kit_Price(IV.ITEMNMBR, PL.PRCLEVEL)

    ORDER BY

    IV.ITEMNMBR,

    PL.PRCLEVEL

    -- Update Prices

    UPDATE IV00108 SET

    UOMPRICE = KP.CALPRICE

    FROM

    IV00108 PL

    JOIN @KITPRICES KP

    ON PL.ITEMNMBR = KP.ITEMNMBR

    AND PL.PRCLEVEL = KP.PRCLEVEL

    AND PL.UOFM = KP.UOFM

    WHERE

    KP.CALPRICE > 0

    -- Return Results

    DECLARE @TBL2 TABLE

    (

    ITEMNMBR VARCHAR(25),

    ITEMDESC CHAR(101),

    PRCLEVEL VARCHAR(25),

    UOFM VARCHAR(10),

    QTYBSUOM NUMERIC(19,5),

    UOMPRICE NUMERIC(19,5),

    CALPRICE NUMERIC(19,5),

    ACTUALPRICE NUMERIC(19,5)

    )

    INSERT @TBL2

    SELECT

    KP.*, PL.UOMPRICE ACTUALPRICE

    FROM

    IV00108 PL

    JOIN @KITPRICES KP

    ON PL.ITEMNMBR = KP.ITEMNMBR

    AND PL.PRCLEVEL = KP.PRCLEVEL

    AND PL.UOFM = KP.UOFM

    WHERE

    KP.CALPRICE > 0

    IF ((SELECT COUNT(*) FROM @TBL2) > 0)

    BEGIN

    SET @subject = 'Kit Prices Updated on: ' + CAST(GETDATE() AS VARCHAR(100))

    SET @message = 'The following Kit Prices where updated on - '

    SET @message = @message + CAST(GETDATE() AS VARCHAR(100)) +

    CHAR(13) + CHAR(13)

    CREATE TABLE ##mail_body

    (

    ITEMNMBR VARCHAR(25),

    ITEMDESC CHAR(101),

    PRCLEVEL VARCHAR(25),

    UOFM VARCHAR(10),

    QTYBSUOM NUMERIC(19,5),

    UOMPRICE NUMERIC(19,5),

    CALPRICE NUMERIC(19,5),

    ACTUALPRICE NUMERIC(19,5)

    )

    INSERT ##mail_body

    SELECT * FROM @TBL2

    SET @query = N'

    SELECT

    CAST(PR.PRCLEVEL AS CHAR(4)) [Price Level],

    CAST(PR.ITEMNMBR AS CHAR(12)) [Item],

    PR.ITEMDESC [Description],

    CAST(PR.UOFM AS CHAR(3)) [UOM],

    CAST(PR.UOMPRICE AS NUMERIC(5,2)) [Old Price],

    CAST(PR.CALPRICE AS NUMERIC(5,2))

    [New Price]

    FROM

    ##mail_body PR

    ORDER BY

    PR.PRCLEVEL,

    PR.ITEMNMBR'

    EXEC master..xp_sendmail

    @recipients = @recipients,

    @message = @message,

    @query = @query,

    @subject = @subject,

    @width = 200 -- Changed from 150 to see if the e-mail's sent will look better, Alexis Thomas Aug 16th 06

    DROP TABLE ##mail_body

    END

    ELSE

    BEGIN

    SET @subject = 'No Kit Prices were found to update at: '

    + CAST(GETDATE() AS VARCHAR(100))

    SET @message = 'No Kit Prices were updated at - '

    + CAST(GETDATE() AS VARCHAR(100)) + CHAR(13) + CHAR(13)

    EXEC master..xp_sendmail

    @recipients = @recipients,

    @message = @message,

    @subject = @subject,

    @width = 200 -- Changed from 150 to see if the e-mail's sent will look better, Alexis Thomas Aug 16th 06

    RETURN 0

    END

    Regards
    Sushant Kumar
    MCTS,MCP

  • why doesn't that error comes, when i run manually??

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • You could add some auditing to it, and save the output to a text file so you can read where the error occurs.

    Add a few PRINT 'starting step x' lines in between each insert/update, and then next time it fails, open the log file and see which step it broke at. You can probably narrow it down from there.

  • what is the failure msg you geting?

    Are you running this job under correct operator?

    ----------
    Ashish

  • @ derrick

    Can you please elaborate, how to narrow down the error.. the print statements script ? and at where...

    @ ashish

    The error i wrote at starting the post.

    It runs under sa.

    Regards,

    SUSHANT

    Regards
    Sushant Kumar
    MCTS,MCP

  • Add an output file to the Job Step (under Advanced)

    In the job script after each statemet (like -- Table to hold Price Changes etc.) add a PRINT statement like

    PRINT 'Starting -- Table to hold Price Changes'

    This will help you to narrow down the issue.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • @ pradeep

    Thanks a lot

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

Viewing 10 posts - 1 through 9 (of 9 total)

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