Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help on SP Expand / Collapse
Author
Message
Posted Tuesday, October 01, 2013 1:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:59 PM
Points: 55, Visits: 163
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?
Post #1500561
Posted Tuesday, October 01, 2013 1:45 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 3,309, Visits: 2,353
MSSQL_NOOB (10/1/2013)
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?

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



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1500575
Posted Tuesday, October 01, 2013 2:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:59 PM
Points: 55, Visits: 163
Awesome! Awesome! That worked.

Thanks a bunch.
Post #1500587
Posted Tuesday, October 01, 2013 2:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 3,309, Visits: 2,353
MSSQL_NOOB (10/1/2013)
Awesome! Awesome! That worked.

Thanks a bunch.

No problem. Glad I could help.



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1500590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse