sp_stop_job when invoked from stored procedure is not working

Question

question

sreelatha16 asked Nov 11, '20

sp_stop_job when invoked from stored procedure is not working

Hi All,

I have created a stored procedure and passed jobname to sp_stop_job. This stored procedure is invoked by a SSMS Job. Though the job is running for a long time, sp_stop_job is not stopping the job . But when the same statement (Exec sp_stop_job ) is executed outside of the stored procedure, it is stopping the stuck job.

It looks like a permission issue and please help with the persmissions to be granted to Stored proc/Job/UDF.

SET @Jobname = (SELECT JobName from UDF()

WHERE StatusFlag = 'Stuck'

runtime>= Threshold)

DECLARE @dynSql NVARCHAR(MAX) = ''

SELECT @dynSql += N' Exec msdb.dbo.sp_stop_job ''' + @Jobname + N''''+ CHAR(10) + CHAR(13)

stored-procedures sqlserver2012

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-11-11T19:17:22Z

minivike answered Nov 11, '20

You're missing an AND in your WHERE clause. And all you're doing with the @dynSql variable is creating a string. I would eliminate the last 2 lines and replace them with

Exec msdb.dbo.sp_stop_job @Jobname

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

minivike commented · Nov 11, 2020 at 07:19 PM

Additionally, if you expect more than one result from your top query, you could wrap this in a Cursor.

0 ·

Answer 2 · 2020-11-12T22:28:01Z

@Sree answered Nov 12, '20

Thanks a lot. It helped me to solve the issue

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question