How to kill list of jobs using dynamic job name in SQL Server

c3frrgcw 于 2023-04-19 发布在 SQL Server

关注(0)|答案(1)|浏览(155)

i stored list of jobs in temp table and that need to be killed.

use msdb
declare @counts int, @jobname nvarchar(1000), @cmd nvarchar(max)
set @counts = (select count(*) from #jobslist)
while @counts>=1
begin
set @jobname = (select name from #jobslist where rnk=@counts)

set @cmd = 'use msdb EXEC dbo.sp_stop_job N'+''''+@jobname+''''
--select @jobname
print @cmd
exec @cmd
set @counts=@counts-1
end

and getting this error

use msdb EXEC dbo.sp_stop_job N'JOBA' Msg 2812, Level 16, State 62, Line 30

Could not find stored procedure 'use msdb EXEC dbo.sp_stop_job N'JobA''.

sql-server

来源：https://stackoverflow.com/questions/76002071/how-to-kill-list-of-jobs-using-dynamic-job-name-in-sql-server

1条答案

按热度按时间

d4so4syb1#

Could you try this?

use msdb
declare @counts int, @jobname nvarchar(1000), @cmd nvarchar(max)
set @counts = (select count(*) from #jobslist)
while @counts>=1
begin
set @jobname = (select name from #jobslist where rnk=@counts)

set @cmd = 'EXEC dbo.sp_stop_job N'+''''+@jobname+''''
--select @jobname
print @cmd
exec @cmd
set @counts=@counts-1
end

DECLARE @counts INT, @jobname NVARCHAR(1000), @cmd NVARCHAR(MAX);

-- Switch to the msdb database
USE msdb;

SET @cmd = 'EXEC dbo.sp_stop_job N''test''';

-- Execute the command
EXEC sp_executesql @cmd;

USE msdb;
DECLARE @counts INT, @jobname NVARCHAR(1000), @cmd NVARCHAR(MAX);

SET @jobname = 'test';

SET @cmd = N'EXEC msdb.dbo.sp_stop_job @job_name';
    
EXEC sp_executesql @cmd, N'@job_name NVARCHAR(1000)', @job_name = @jobname;

赞(0）回复(0）举报 2023-04-19

我来回答

How to kill list of jobs using dynamic job name in SQL Server

1条答案

相关问题

热门标签

最新问答