Scheduled Job Failing

22

I could be way off base here but here goes anyway. Your function has several variable assignments based on selects. If any one of them returns more than one row for any case id the function will fail. Your procedure looks for an error after the function but just does a rollback and a return -1 but does not raise anything back to the job which could be masking the the error and so the job just fails with no error descripiton.

I would look at each of the variable assignments and see if any case id's return more than one row and add a raiseerror before the return to make sure an error description gets raised back to the job.

1 Like
23

Sorry for the delay in response. I did not now first day noobies only gets a certain amount of post per day. I ran the query in prod with the PRINT. In the messages the last row says:

(1 row(s) affected)
** 255**
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

24

This is a different error than the one we are looking for. It has nothing to do with the casenumberid we are trying to track down.
We are trying to find this error
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Either test the code in Prod or refresh dev with latest data from prod

25

The only way I will get MSG512 (subquery error) is if I execute the stored procedure directly. I have tried this code in prod:

declare @casenumberid int;

DECLARE cases_Cursor CURSOR FOR  
SELECT dcasenumberid
FROM casedates 

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

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

I am running that query again but Results as Text to remove the outofmemoryexception

26

ahhhh my bad! Apologies. how about trying this one

create table #dupchecker(casenumberid int);

insert into #dupchecker
SELECT distinct casenumberid
FROM casedates 

;with cteDupChecker
as
(
select casenumberid, dbo.fn_calculatecasehealth(casenumberid)
  FROM #dupchecker
)
select casenumberid, count(*) 
  from cteDupChecker
  group by casenumberid
  having count(1) > 1

The issue is in the update statement because for one of the casenumberIds there is some duplicate results coming back from the udf

27

As you can see in this example you get the same error as you are from your SQL Job because var @shoore returns 2 rows

create table #dupsubquery(bootleg varchar(50))

insert into #dupsubquery
select 'A' union all
select 'A'



create table #pond(updateme varchar(50))

insert into #pond
select 'Frogs' union
select 'Jump'


declare @shoore varchar(50)

SELECT @shoore = (SELECT bootleg FROM #dupsubquery WHERE bootleg = 'A')

update #pond set updateme = @shoore

drop table #dupsubquery
drop table #pond
28

I am getting ready to run the dupchecker script. I copied and pasted it to SSMS Query for execution, however I get a red squiggly under cteDupChecker for column not specified error. Am I doing something wrong? Thank you.

dupchecker

29

:roll_eyes:

you have to push it to the next line

30

what do you mean push it to the next line? Sorry, I'm kind of new to this. thanks

31

Make it so that it matches this exactly

create table #dupchecker(casenumberid int);

insert into #dupchecker
SELECT distinct casenumberid
FROM casedates 

;with cteDupChecker
as
(
select casenumberid, dbo.fn_calculatecasehealth(casenumberid)
  FROM #dupchecker
)
select casenumberid, count(*) 
  from cteDupChecker
  group by casenumberid
  having count(1) > 1
32

I matched what you have and executed. It returned the following error at the every bottom dupchecker2

33

add
fn_calculatecasehealth(casenumberid) as calc

34

Like this below?

 create table #dupchecker(casenumberid int);

    insert into #dupchecker
    SELECT distinct casenumberid
    FROM casedates 

    ;with cteDupChecker
    as
    (
    select casenumberid, dbo.fn_calculatecasehealth(casenumberid) as calc
      FROM #dupchecker
    )
    select casenumberid, count(*) 
      from cteDupChecker
      group by casenumberid
      having count(1) > 1
35

Also, where does this object #dupchecker go in the DB? When I tried to run the code again it says object already exists in the DB. thanks.

36

add this at the end
drop table #dupchecker

run it first by itself, then rerun whole thing again. # indicates a temp table

37

I deleted the #dupchecker and reran the script with a drop table #dupchecker at the end. Query was successful, no results were returned.

38

Do you guys see anything wrong with this trigger?

CREATE TRIGGER trig_casenumberspermissions_add ON dbo.casenumberspermissions
WITH ENCRYPTION
AFTER INSERT AS
BEGIN
      
       DECLARE @tempstaffuserid int, @tempcasenumberid int
       DECLARE @tempcreatorid int, @tempdisplayedcasenumber varchar(32)
      
 
       IF (1 = (SELECT COUNT(ins.casenumberid) FROM inserted ins))
       BEGIN
 
              --Retrieve case number Id and displayed case number
              SELECT @tempcasenumberid = (SELECT ins.casenumberid FROM inserted ins)
              SELECT @tempstaffuserid = (SELECT ins.staffuserid FROM inserted ins)
              SELECT @tempcreatorid = (SELECT ins.creatorid FROM inserted ins)
 
              --Notify if needed
              IF (dbo.fn_checkallaccesspermissionsnotification(@tempstaffuserid) > 0)
              BEGIN
                     EXEC notificationallaccesscaseassignment_add_proc
                           @tempstaffuserid, @tempcreatorid, @tempcasenumberid
              END
       END
 
END
 
GO
39

any other suggestions?