SQL query to iterate over schedule job and delete it. The stored procedure will take Job_Name as Input parameter

user_15996335 0
Viewed 28

I am working on a problem where I get a job_id and job name and delete a scheduled job using a stored procedure. Here I need to pass job name as input parameter.

I am using

select job_id, name from msdb.dbo.sysjobs

and get this:

job_id name
sjkfaih usp_1
fjoajff usp_2

I want to delete the stored procedure by name instead of job_id as it become little complex.

I want the stored procedure to take job_name as a parameter but return Job_id so that it I can execute it by using

EXEC msdb.dbo.sp_delete_job @job_id = N'@JobId', @delete_unused_schedule = 1

This is my attempt:

DECLARE @JobName nvarchar(MAX)
DECLARE @JobId NVARCHAR(MAX)

DECLARE Job_ID, Job_Name CURSOR FOR
    SELECT job_id, name 
    FROM msdb.dbo.sysjobs;

OPEN Job_ID, Job_Name;

FETCH NEXT FROM Job_ID, Job_Name INTO @JobId, @JobName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_id = N'@JobId', @delete_unused_schedule = 1

    FETCH NEXT FROM Job_ID INTO @JobId;
END

CLOSE Job_ID;
DEALLOCATE Job_ID;

I think I am doing something wrong as I am getting a syntax error. Please suggest me some improvements. TIA.

sql-server
1 Answers

You need to give your cursor a valid name - not a list of columns ....

Try this code:

DECLARE @JobName sysname, @JobId uniqueidentifier;

-- give your cursor a proper NAME
DECLARE JobIdNameCursor CURSOR FOR
    SELECT job_id, name 
    FROM msdb.dbo.sysjobs;

-- open the cursor by NAME    
OPEN JobIdNameCursor;

-- fetch from cursor by NAME
FETCH NEXT FROM JobIdNameCursor INTO @JobId, @JobName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_id = N'@JobId', @delete_unused_schedule = 1;

    FETCH NEXT FROM JobIdNameCursor INTO @JobId, @JobName;
END

-- close and deallocate that cursor - again by its proper NAME
CLOSE JobIdNameCursor;
DEALLOCATE JobIdNameCursor;
user_133020
Related