Scheduled Job Failing

1

We have been recently receiving email alerts that a job is failing. We have never gotten this alert before and not sure what changed. This job runs a stored procedure which evaluates multiple fields in the database to calculate one field 'case health' (decides how healthy the case is). The stored procedure also contains a function. How do I trace what is causing this issue?

Thank you.

EMAIL NOTIFICATION:

JOB RUN: 'Update CMS Case Health' was run on 3/12/2018 at 8:10:00 PM
DURATION: 0 hours, 37 minutes, 22 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 12 (Update Case Health Schedule). The last step to run was step 1 (Update Case Health).

2

is it running any other SSIS packages? or just stored procedure?
If just sproc right click on job to find out more details under Properties and post back

1 Like
4

It seems like just a pretty simple Job with 1 step (the only step created). The step type is transact-sql and the command is

casedatesii_edit_proc 'Not Used', 'Not Used', 'Case Health Update', '1/1/1900'

I believe those are the default values inputted for casedatesii_edit_proc (procedure) for the evaluation.

5

hard to help you when we do not know what the error says

6

Yeah I know. That's what I've been trying to figure out the past few days. Even the history log on the job shows basically the same message as the email notification.

So another thing I did was right-click on the stored procedure and executed it directly. I manually keyed in the 4 values that were listed in the job. After I ran the SP, it returned an MSG 512 Line 16...and points to a Trigger Line 18. I am not sure if this is the equivalent way/result as running the job. However, I went to what I thought was the trigger and deleted it completely, ran the SP again and still gave me the same result referencing to the Trigger. Not sure if this helps?

7

This is the SP by the way.

CREATE PROCEDURE casedatesii_edit_proc
       (@tempuseraccountname varchar(64), @tempcomputername varchar(100),
       @tempuniquesystemidentifier varchar(100), @tempclienttime datetime)
WITH ENCRYPTION
AS
       DECLARE @transactionname varchar(30)
 
--     Perform initialization.
       SET @transactionname = 'Edit_CasesDatesHealth_Tran'
 
                    
       BEGIN TRAN @transactionname
 
              UPDATE casedates SET casehealth = dbo.fn_calculatecasehealth(casedates.casenumberid)
 
              IF @@ERROR !=0
              BEGIN
                     ROLLBACK TRAN @transactionname
                     RETURN -1
              END
 
       COMMIT TRAN @transactionname
      
       RETURN 0
 
GO
8

I hope it is not a production trigger.

  1. Right click on SQL Job that is failing
  2. Select View History
  3. Click and expand the last failed History [+]
  4. Then select the failed step and at the bottom you will the details of what is the actual error of the failed job
1 Like
9

The details shows this:

Executed as users: [Server]\DBASQLAgent. 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)Unable to open Step output file. The step failed.

10

so it sounds like somewhere in your function fn_calculatecasehealth(casedates.casenumberid) there is some subquery causing some issues

1 Like
11

That's the strange part, we never made any changes to those functions and SP for years and all of a sudden it's doing this. Here's the fn_calculatecasehealth :

CREATE FUNCTION fn_calculatecasehealth
       (@tempcasenumberid int)
RETURNS int
WITH ENCRYPTION
AS
BEGIN
      
       DECLARE @operationresult_casehealth int
       DECLARE @local_caseisactive int, @local_currentprocessowner int
       DECLARE @local_receivedrequestforinvestigation datetime, @local_discoverydate datetime, @local_misconductdate datetime
       DECLARE @local_regionalofficecaseassignment datetime
       DECLARE @local_statuteoflimitationdate datetime
 
       DECLARE @tempcasedatestable table(casedatesid int, casenumberid int, caseisactive bit,
                           currentprocessowner int, prioritylevelid int, casehealth int, iscentralintakecomplete bit,
                           isinvestigationcomplete bit, isdisciplinecomplete bit, receivedrequestforinvestigation datetime,
                           centralintakecasecalendar datetime, centralintakecaseagentassignment datetime, centralintakepaneldecision datetime,
                           centralintakecomplete datetime, regionalofficecaseassignment datetime, investigatorcaseassignment datetime,
                           investigationcomplete datetime, disciplinecomplete datetime, reportedmisconductdate datetime,
                           reporteddiscoverydate datetime, misconductdate datetime, discoverydate datetime, originallysigneddate datetime,
                           requesteddate datetime, isemploymentadvocacyprosecutionteamcomplete bit, employmentadvocacyprosecutionteamcomplete datetime,
                           verticaladvocatecaseassignment datetime, assesscriticalcasedatescomplete datetime, casecloseddate datetime);
 
 
--     Set default case health
       SET @operationresult_casehealth = 0
 
 
--     Acquire case dates information
       INSERT INTO @tempcasedatestable(casedatesid, casenumberid, caseisactive, currentprocessowner,
                                  prioritylevelid, casehealth, iscentralintakecomplete, isinvestigationcomplete, isdisciplinecomplete, receivedrequestforinvestigation,
                                  centralintakecasecalendar, centralintakecaseagentassignment, centralintakepaneldecision,
                                  centralintakecomplete, regionalofficecaseassignment, investigatorcaseassignment,
                                  investigationcomplete, disciplinecomplete, reportedmisconductdate,
                                  reporteddiscoverydate, misconductdate, discoverydate, originallysigneddate,
                                  requesteddate, isemploymentadvocacyprosecutionteamcomplete, employmentadvocacyprosecutionteamcomplete,
                                  verticaladvocatecaseassignment, assesscriticalcasedatescomplete, casecloseddate)
       SELECT casedatesid, casenumberid, caseisactive, currentprocessowner,
                     prioritylevelid, casehealth, iscentralintakecomplete, isinvestigationcomplete, isdisciplinecomplete, receivedrequestforinvestigation,
                     centralintakecasecalendar, centralintakecaseagentassignment, centralintakepaneldecision,
                     centralintakecomplete, regionalofficecaseassignment, investigatorcaseassignment,
                     investigationcomplete, disciplinecomplete, reportedmisconductdate,
                     reporteddiscoverydate, misconductdate, discoverydate, originallysigneddate,
                     requesteddate, isemploymentadvocacyprosecutionteamcomplete, employmentadvocacyprosecutionteamcomplete,
                     verticaladvocatecaseassignment, assesscriticalcasedatescomplete, casecloseddate
       FROM casedates
       WHERE casenumberid = @tempcasenumberid
 
 
--     Acquire statute of limitation date
       SET @local_statuteoflimitationdate = dbo.fn_calculatestatuteoflimitationdate(@tempcasenumberid)
 
 
--     Case is active
       SELECT @local_caseisactive = (SELECT caseisactive FROM casedates WHERE casenumberid = @tempcasenumberid)
 
--     Current Process Owner
       SELECT @local_currentprocessowner = (SELECT currentprocessowner FROM casedates WHERE casenumberid = @tempcasenumberid)
 
--     Received for Request for Investigation
       SELECT @local_receivedrequestforinvestigation = (SELECT receivedrequestforinvestigation FROM casedates WHERE casenumberid = @tempcasenumberid)
 
--     Discovery Date
       SELECT @local_discoverydate = (SELECT discoverydate FROM casedates WHERE casenumberid = @tempcasenumberid)
 
--     Misconduct Date
       SELECT @local_misconductdate = (SELECT misconductdate FROM casedates WHERE casenumberid = @tempcasenumberid)
 
--     Regional Office Assignment
       SELECT @local_regionalofficecaseassignment = (SELECT regionalofficecaseassignment FROM casedates WHERE casenumberid = @tempcasenumberid)
 
 
 
       IF ((1=@local_currentprocessowner) AND (DATEDIFF(dd, @local_receivedrequestforinvestigation, GETDATE()) >= 14)) --CentralIntake=1 (14/30 Day warning)
       BEGIN
              SET @operationresult_casehealth = 2 --Warning=2
       END
       ELSE IF ((1=@local_currentprocessowner) AND (DATEDIFF(dd, @local_receivedrequestforinvestigation, GETDATE()) > 0) AND
                     (DATEDIFF(dd, @local_receivedrequestforinvestigation, GETDATE()) < 14))
       BEGIN
              SET @operationresult_casehealth = 1 --Normal=1
       END
       ELSE IF ((1=@local_currentprocessowner) AND (DATEDIFF(dd, @local_receivedrequestforinvestigation, GETDATE()) < 0))
       BEGIN
              SET @operationresult_casehealth = 0 --Unknown=0
       END
       ELSE IF ((2=@local_currentprocessowner) AND (DATEDIFF(dd, @local_regionalofficecaseassignment, GETDATE()) >= 120))  --(60 investigative days left)
       BEGIN
              SET @operationresult_casehealth = 2 --Warning=2
       END
       ELSE IF ((2=@local_currentprocessowner) AND (DATEDIFF(dd, @local_regionalofficecaseassignment, GETDATE()) > 0) AND --(More than 180 Days before SOL)
                     (DATEDIFF(dd, @local_regionalofficecaseassignment, GETDATE()) < 120)) 
       BEGIN
              SET @operationresult_casehealth = 1 --Normal=1
       END
       ELSE IF ((2=@local_currentprocessowner) AND (DATEDIFF(dd, @local_regionalofficecaseassignment, GETDATE()) < 0))
       BEGIN
              SET @operationresult_casehealth = 0 --Unknown=0
       END
       ELSE IF ((3=@local_currentprocessowner) AND
                     ((@local_discoverydate < CONVERT(datetime, '1/1/1950')) OR (@local_misconductdate < CONVERT(datetime, '1/1/1950'))))
       BEGIN
              SET @operationresult_casehealth = 0 --Unknown=0
       END
       ELSE IF ((3=@local_currentprocessowner) AND (DATEDIFF(dd, GETDATE(), @local_statuteoflimitationdate) <= 60))  --Discipline (60 days before SOL)
       BEGIN
              SET @operationresult_casehealth = 2 --Warning=2
       END
       ELSE IF ((3=@local_currentprocessowner) AND (DATEDIFF(dd, GETDATE(), @local_statuteoflimitationdate) > 60))  --(More than 60 days before SOL)
       BEGIN
              SET @operationresult_casehealth = 1 --Normal=1
       END
 
 
--     Statute date has been blown
       IF ((DATEDIFF(dd, GETDATE(), @local_statuteoflimitationdate) < 0) AND
              (@local_discoverydate > CONVERT(datetime, '1/1/1950')) AND (@local_misconductdate > CONVERT(datetime, '1/1/1950')))
       BEGIN
              SET @operationresult_casehealth = 3 --Critical=3
       END
 
 
--     Set case health to Normal if the case is closed (last conditional)
       IF (0 = @local_caseisactive)
       BEGIN
              SET @operationresult_casehealth = 1 --Normal=1
       END
 
 
       RETURN @operationresult_casehealth
 
END
 
GO
12

what does this return?

SELECT casenumberid FROM casedates group by casenumberid having count(*) >1

SELECT currentprocessowner FROM casedates group by currentprocessowner having count(*) > currentprocessowner

SELECT receivedrequestforinvestigation FROM casedates group by receivedrequestforinvestigation having count(*) > receivedrequestforinvestigation

SELECT discoverydate FROM casedates group by discoverydate having count(*) > discoverydate

SELECT misconductdate FROM casedates group by misconductdate having count(*) > misconductdate

SELECT regionalofficecaseassignment FROM casedates group by regionalofficecaseassignment having count(*) > regionalofficecaseassignment
13

or something like this to find the offending casenumberid

declare @casenumberid int;

DECLARE cases_Cursor CURSOR FOR  
SELECT casenumberid
FROM casedates 

OPEN cases_Cursor;  
FETCH NEXT FROM cases_Cursor
INTO @casenumberid
 
WHILE @@FETCH_STATUS = 0  
   BEGIN  
		select @casenumberid
		select dbo.fn_calculatecasehealth(@casenumberid)

      FETCH NEXT FROM cases_Cursor
	  INTO @casenumberid;  
   END;  
CLOSE cases_Cursor;  
DEALLOCATE cases_Cursor;  
GO

when this errors out look at what the casenumberid is.

14

I ran each of your SELECT statements individually and here are the returned row results respectively:

  • 0 row
  • 4 rows
  • 2 rows
  • 1 row
  • 5 rows
  • 1 row
15

So since you are the one that knows the business rules does one of those numbers cause you to say ah I know the problem. If not try this

declare @casenumberid int;

DECLARE cases_Cursor CURSOR FOR  
SELECT casenumberid
FROM casedates 

OPEN cases_Cursor;  
FETCH NEXT FROM cases_Cursor
INTO @casenumberid
 
WHILE @@FETCH_STATUS = 0  
   BEGIN  
		select @casenumberid
		select dbo.fn_calculatecasehealth(@casenumberid)

      FETCH NEXT FROM cases_Cursor
	  INTO @casenumberid;  
   END;  
CLOSE cases_Cursor;  
DEALLOCATE cases_Cursor;  
GO

Try it in SSMS, then when it fails go to your Messages tab and see what the last casenumberid is

16

I ran this query against dev server and it completed with errors. I looked at the messages tab and it went through 1237 rows. The error message states:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

17

Does Dev have the same data as prod?

Change things to

DECLARE cases_Cursor CURSOR FOR
SELECT distinct casenumberid
FROM casedates

and run again

18

The Dev was a backup of Prod from 4 months ago. Although Dev does not have the scheduled Job, it has all over the procedures and functions just like Prod.

I added the DISTINCT before casenumberid and I still get the same Message error but with 992 rows. In the Results tab, the last records shows this (not sure if it helps?)

results

19
  1. Try it in SSMS, then when it fails go to your Messages tab and see what the last casenumberid is
    (I dont mean any failure I meant to say the failure that happens on the job
    This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    ) Your other failure has nothing to do with the original issue we are trying to resolve.
  2. Does Dev have the same data as prod
20

I ran it in SSMS and the Messages tab will only show "(1 row(s) affected) " 992 times. At the end it shows An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

The Dev data is the same except it hasn't been updated in 4 months.

21
  1. This error has nothing to do with the original error we are trying to resolve. Try the test in production
  2. so dev does not have the same data as prod because it was updated 4 months ago. Otherwise we will not be able to find the source of your job failure because there could be a case number in production that is not in your dev data, you see what I mean?
  3. change this select @casenumberid to PRINT @casenumberid