如何在SQL Server中使用动态作业名杀死作业列表

Question

i存储了临时表中需要终止的任务列表。

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

并得到这个错误

use msdb EXEC dbo。sp_stop_job N 'JOBA’Msg 2812, 16层，62州，30行

无法找到使用msdb EXEC数据库的存储过程。sp_stop_job N 'JobA"。

Answer 1

你能试试吗?

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;