I am trying to check to see if competing SQL Jobs are running since they share the same table. We want to ensure that one does not block the other by locking the table.

The problem is we have a job that runs every 15secs and we check to see if a job is already running first. But no matter what (meaning if there is not a job running) it will not run. However if I take the EXACT same query and manually run it in SSIS, it runs perfectly.

Here is the query:

declare @return_value int,
    @messagesUpdated int,
    @dlrsDeleted int
    
if not exists ( 
    select 1
    from msdb.dbo.sysjobs_view job 
    inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
    where 
        activity.run_Requested_date is not null 
    and activity.stop_execution_date is null 
    )
    
begin
exec @return_value = [dbo].[updateDeliveryReceipts]
    @messagesUpdated = @messagesUpdated OUTPUT,
    @dlrsDeleted = @dlrsDeleted OUTPUT
end
select  @messagesUpdated as N'@messagesUpdated',
    @dlrsDeleted as N'@dlrsDeleted'

select  'Return Value' = @return_value

But if I take this check to see if a Job is currently running…. it runs just fine.

if not exists (  
    select 1
    from msdb.dbo.sysjobs_view job 
    inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
    where 
        activity.run_Requested_date is not null 
    and activity.stop_execution_date is null 
)

I know this is probably a simple fix… but for the life of me cannot figure it out.

Any assistance would be greatly appreciated!

Thanks

SQL Server 2017 Standard running on Windows Server