October 1, 2013 at 1:03 pm
I'm trying to create an SP to execute the following
msdb.dbo.sp_add_jobstep
msdb.dbo.sp_update_jobstep
A 3rd party software installer created about 300-400 jobs. And naturally, the jobs aren't emailing out when it fails. So I'm trying to add a step to the job and alter the 1st steps of the job.
I can run this for each job replacing the job_name with the job name that got created; however, it'll take forever.
USE MSDB
GO
EXEC sp_add_jobstep
@job_name = N'InstallerJob1',
@step_name = N'EmailOut',
@subsystem = N'TSQL',
@command = N'EXEC sp_FailedJob ''InstallerJob1''',
@on_success_action = 2
GO
EXEC sp_update_jobstep
@job_name = N'InstallerJob1',
@step_id=1,
@on_fail_action = 4,
@on_fail_step_id = 2
GO
However, I thought it'll be quicker to execute an SP.
This is the SP I have so far.
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_Steps
WITH ENCRYPTION
AS
--Purpose: To create job step email notification for
--jobs created automatically from installer.
DECLARE
@jobneed VARCHAR(150)
BEGIN -- begin sp
DECLARE job_cursor CURSOR FOR
SELECT name FROM msdb.dbo.sysjobs
WHERE name NOT LIKE 'BACKUP%'
AND name NOT LIKE 'sys%'
ORDER BY name;
OPEN job_cursor
FETCH NEXT FROM job_cursor
INTO @jobneed
WHILE @@FETCH_STATUS = 0
BEGIN -- begin fetch
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'@jobneed',
@step_name = N'EmailOut',
@subsystem = N'TSQL',
@command = N'EXEC sp_FailedJob ''@jobneed''',
@on_success_action = 2
EXEC msdb.dbo.sp_update_jobstep
@job_name = N'@jobneed',
@step_id=1,
@on_fail_action = 4,
@on_fail_step_id = 2
END -- end fetch
close job_cursor
deallocate job_cursor
END -- end proc
GO
Getting the following errors:
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('@jobneed') does not exist.
I think I'm just missing passing the jobneed variable into the param. Am I on the right path?
October 1, 2013 at 1:45 pm
MSSQL_NOOB (10/1/2013)
I'm trying to create an SP to execute the followingmsdb.dbo.sp_add_jobstep
msdb.dbo.sp_update_jobstep
A 3rd party software installer created about 300-400 jobs. And naturally, the jobs aren't emailing out when it fails. So I'm trying to add a step to the job and alter the 1st steps of the job.
I can run this for each job replacing the job_name with the job name that got created; however, it'll take forever.
USE MSDB
GO
EXEC sp_add_jobstep
@job_name = N'InstallerJob1',
@step_name = N'EmailOut',
@subsystem = N'TSQL',
@command = N'EXEC sp_FailedJob ''InstallerJob1''',
@on_success_action = 2
GO
EXEC sp_update_jobstep
@job_name = N'InstallerJob1',
@step_id=1,
@on_fail_action = 4,
@on_fail_step_id = 2
GO
However, I thought it'll be quicker to execute an SP.
This is the SP I have so far.
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_Steps
WITH ENCRYPTION
AS
--Purpose: To create job step email notification for
--jobs created automatically from installer.
DECLARE
@jobneed VARCHAR(150)
BEGIN -- begin sp
DECLARE job_cursor CURSOR FOR
SELECT name FROM msdb.dbo.sysjobs
WHERE name NOT LIKE 'BACKUP%'
AND name NOT LIKE 'sys%'
ORDER BY name;
OPEN job_cursor
FETCH NEXT FROM job_cursor
INTO @jobneed
WHILE @@FETCH_STATUS = 0
BEGIN -- begin fetch
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'@jobneed',
@step_name = N'EmailOut',
@subsystem = N'TSQL',
@command = N'EXEC sp_FailedJob ''@jobneed''',
@on_success_action = 2
EXEC msdb.dbo.sp_update_jobstep
@job_name = N'@jobneed',
@step_id=1,
@on_fail_action = 4,
@on_fail_step_id = 2
END -- end fetch
close job_cursor
deallocate job_cursor
END -- end proc
GO
Getting the following errors:
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('@jobneed') does not exist.
I think I'm just missing passing the jobneed variable into the param. Am I on the right path?
Unquote the @jobneed that you pass to the execute. Another important point it to fetch the next row from the cursor so you don't update the same job infinitely. In other words:
OPEN job_cursor
FETCH NEXT FROM job_cursor
INTO @jobneed
WHILE @@FETCH_STATUS = 0
BEGIN -- begin fetch
EXEC msdb.dbo.sp_add_jobstep
@job_name = @jobneed,
@step_name = N'EmailOut',
@subsystem = N'TSQL',
@command = N'EXEC sp_FailedJob ''@jobneed''',
@on_success_action = 2
EXEC msdb.dbo.sp_update_jobstep
@job_name = @jobneed,
@step_id=1,
@on_fail_action = 4,
@on_fail_step_id = 2
FETCH NEXT FROM job_cursor INTO @jobneed
END -- end fetch
close job_cursor
I underlined the parts I changed.
HTH
October 1, 2013 at 2:01 pm
Awesome! Awesome! That worked.
Thanks a bunch.
October 1, 2013 at 2:04 pm
MSSQL_NOOB (10/1/2013)
Awesome! Awesome! That worked.Thanks a bunch.
No problem. Glad I could help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply