remove a step from sql agent jobs

Question

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%'

Answer 1

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

Answer 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%'