locked
SQL Agent Job Reports Failures but Job Actually Succeeds RRS feed

  • Question

  • We have a SQL Server Agent job schedule to execute stored procedure which populate raw data to reporting table.

    The job "occasionally" quit the job reporting failure with the following error message :

    Message
    Executed as user: PROD\sqlserveragent. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

    I have checked whether due to subquery issue as stated above by using profiler but the stored procedure actually ran until last statement in the stored procedure without error based on the trace in the profiler.

    Temporary solution is I stopped the job then start again. It ran successfully and move to next step.

    May I have some suggestions :

    1. I have used profiler to trace to SQL : BatchStarting and checked it ran until last statement in the stored procedure. Any others tool or method that I can use to troubleshoot this error ?

    2. Can we brainstorm what is the problem ? It seems the error message returned from the job 'not correct' .

    Thank you.

    Thursday, August 22, 2013 11:12 AM

Answers

  • SQL wouldn't provide an error statement like that without reason.

    What I would suggest is that you find any parts in your code that have a subquery which is expecting just 1 value (such as "where X = (select id from...)" and then surround that entire statement with a TRY...CATCH block.

    This way, in your CATCH block you can log your output to a table.  Therefore when you want to know what happened you will be able to look in the log table and see what values SQL was receiving.

    When you have re-run the query, it's possible that the underlying data has changed between failure and re-running the query and therefore you're seeing correct execution.

    For TRY and CATCH syntax, please refer here:

    http://technet.microsoft.com/en-us/library/ms175976.aspx

    Thanks

    • Proposed as answer by Kevin Urquhart Thursday, August 22, 2013 11:58 AM
    • Marked as answer by Simly Friday, August 23, 2013 7:12 AM
    Thursday, August 22, 2013 11:21 AM

All replies

  • >Subquery returned more than 1 value. This is not permitted when the subquery follows

    This is a real bug in a T-SQL query.

    You need to debug the stored procedure.  Post the logic for quick help.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    • Edited by Kalman Toth Thursday, August 22, 2013 11:18 AM
    Thursday, August 22, 2013 11:18 AM
  • SQL wouldn't provide an error statement like that without reason.

    What I would suggest is that you find any parts in your code that have a subquery which is expecting just 1 value (such as "where X = (select id from...)" and then surround that entire statement with a TRY...CATCH block.

    This way, in your CATCH block you can log your output to a table.  Therefore when you want to know what happened you will be able to look in the log table and see what values SQL was receiving.

    When you have re-run the query, it's possible that the underlying data has changed between failure and re-running the query and therefore you're seeing correct execution.

    For TRY and CATCH syntax, please refer here:

    http://technet.microsoft.com/en-us/library/ms175976.aspx

    Thanks

    • Proposed as answer by Kevin Urquhart Thursday, August 22, 2013 11:58 AM
    • Marked as answer by Simly Friday, August 23, 2013 7:12 AM
    Thursday, August 22, 2013 11:21 AM
  • This must be an issue at the time of job execution you have multiple data in the table that you reffered in a subquery. To troubleshoot, you need to find out the subqueries use din your procedure and see if you use "=".

    Something like:

    Select * From table Where id =(Select id from table1) --where table1 returns more than 1 value.

    If you can find such queries, next step is to find a reason to get multiple records to return from the table. Or you can use TOP in your subquery.But that might not be a suitable solution align with the business logic. Or you can use IN instead of "=". It all depends on your logic. Top of everything, I would suggest to find the reason for multiple values in the subquery return if its not desired. This may be a timely data issue.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 22, 2013 11:23 AM
  • Thanks. But the code already using IN and TOP 1. 
    Friday, August 23, 2013 7:38 AM