none
having issues with the agent job failure RRS feed

  • Question

  • Date 12/2/2019 2:40:07 AM
    Log Job History (DonorStudio - ETL Letters - Daily)

    Step ID 4
    Server
    Job Name DonorStudio - ETL Letters - Daily
    Step Name exec dbo.prcCheckTblKPDataFinal
    Duration 00:00:00
    Sql Severity 11
    Sql Message ID 50000
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: . Possible Bad data in tblKPDataFinal.  See prcCheckTblKPDataFinal [SQLSTATE 42000] (Error 50000).  The step failed.

    This job failed to excute please what can i do to get it back running and what must have caused this issue 

    Monday, December 2, 2019 5:48 PM

All replies

  • The issue may be related to data. I guess the job executes the stored procedure and then do something related to data, i.e., INSERT or UPDATE. Check if data is invalid.

    A Fan of SSIS, SSRS and SSAS

    Monday, December 2, 2019 6:04 PM
  • The error is specific to the job you are running and there is no one where that knows what that job does. You need to find the person in the organisation who knows what this is job is doing. Also have a look at prcCheckTblKPDataFinal - that sounds like the name of a stored procedure.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 2, 2019 10:43 PM
  • Hi sunni121,

    Error 50000 might be a user-defined error.  Check your script to see if contains a RAISERROR statement like:

    Also , we don't know your detailed script of your step . Maybe you can use TRY...CATCH or THROW to collect error. Please check it. 

    /*Create tables */
    
    ----DROP TABLE dbo.test2
    ----DROP TABLE dbo.test 
    CREATE TABLE dbo.test 
    (
    columna int primary key
    )
    GO
    CREATE TABLE dbo.test2
    (
    columnb int 
    )
    GO
    ALTER TABLE dbo.test2  WITH CHECK ADD  CONSTRAINT [FK_test_to_test] FOREIGN KEY(columnb)
    REFERENCES dbo.test  (columna)
    GO
    ALTER TABLE dbo.test2 CHECK CONSTRAINT [FK_test_to_test] 
    GO
    
    --------returns the last error message------
    BEGIN TRY
        ALTER TABLE dbo.test 
        ALTER Column columna varchar
    END TRY
    BEGIN CATCH
        DECLARE  @ERROR_MESSAGE NVARCHAR(2048) = ERROR_MESSAGE()
        RAISERROR (@ERROR_MESSAGE,16,16)
    END CATCH  
    /*
    Msg 50000, Level 16, State 16, Line 27
    ALTER TABLE ALTER COLUMN columna failed because one or more objects access this column.
    */
          
    ------ Returns both messages-----
    BEGIN TRY
        ALTER TABLE dbo.test 
        ALTER Column columna varchar
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH   
    /*
    Msg 5074, Level 16, State 1, Line 36
    The object 'PK__test__DB06C17D6277C3BB' is dependent on column 'columna'.
    Msg 5074, Level 16, State 1, Line 36
    The object 'FK_test_to_test' is dependent on column 'columna'.
    Msg 4922, Level 16, State 9, Line 36
    ALTER TABLE ALTER COLUMN columna failed because one or more objects access this column.
    */    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 3, 2019 5:39 AM