-1

I copied a bunch of jobs from an HA server to a regular server and I need to delete only the first step( checks for the primary) on these jobs not all jobs. Any suggestion? this is the query I used to find them

SELECT 
 a.job_id
,name
,enabled
,b.command
,b.step_name
,b.step_id
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobsteps b
ON a.job_id = b.job_id
where step_name like '%check prima%'
3

You could try dynamically generating TSQL to delete the specific step. Here is an example:

SELECT 
'exec msdb.dbo.sp_delete_jobstep @job_id=''' + convert(varchar(50), a.job_id) + '''' + ', @step_id = ' + convert(varchar(30), b.step_id) as CmdToExec ,
 a.job_id
,name
,enabled
,b.command
,b.step_name
,b.step_id
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobsteps b
ON a.job_id = b.job_id
where b.step_name like '%test%' and b.step_id=1

Then, manually run the statements in the CmdToExec column from the select above

CmdToExec
exec msdb.dbo.sp_delete_jobstep @job_id='02CA12AD-FAAC-4788-9D31-14143F0ED739', @step_id = 1
exec msdb.dbo.sp_delete_jobstep @job_id='41B86E98-F033-4847-8104-42FD6B49A21F', @step_id = 1
2

A quicker / safer method may be to update the command on step 1 to run if it's not the primary, or not in a AG, or whatever.

update msdb.dbo.sysjobsteps
set command = 'print ''removed primary check code'''
where command = 'your old code'
and step_id = 1
and step_name like '%check prima%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.