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

changing Agent jobs notification via msdb? Expand / Collapse
Posted Thursday, August 30, 2012 4:39 AM


Group: General Forum Members
Last Login: Tuesday, March 11, 2014 9:41 AM
Points: 118, Visits: 439
Hi all,

Id like to make sure all my agent jobs update the event log if they error.

If i go to the GUI and change the notification area and script it i get:

USE [msdb]
EXEC msdb.dbo.sp_update_job @job_id=N'7be0b2e1-4404-4fb0-9b93-94a82f31ac5a',
EXEC msdb.dbo.sp_attach_schedule @job_id=N'7be0b2e1-4404-4fb0-9b93-94a82f31ac5a',@schedule_id=8

So i thought i would have to do a cursor to loop though my agent jobs in msdb.

looking at the msdb tables.. to find which jobs need to enabled and which i dont.. I decided to go with the SET approach and decided to try a much easier route of

UPDATE msdb..sysjobs
SET notify_level_eventlog = 2
WHERE notify_level_eventlog = 0

I didnt think you could update system tables.. but this worked.. and looking though my agent jobs in the GUI, they are all now ticked for event log..

Have a done something quite silly and other things needed to be changed for this to work properly.. Or have i found a much simpler solution!

Post #1352092
Posted Friday, August 31, 2012 11:23 AM



Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
n00bDBA (8/30/2012)
Have a done something quite silly and other things needed to be changed for this to work properly.. Or have i found a much simpler solution!

I don't know? Did you?

EXEC msdb.sys.sp_helptext 
@objname = N'dbo.sp_update_job';

I would not get into the habit of updating the tables in msdb directly. Here is a simpler way for you to produce the desired result next time you need to do something like this without using a cursor:

USE msdb;
SET @sql = N'';

SELECT @sql = @sql + 'EXEC msdb.dbo.sp_update_job @job_name=N''' + name + ''',
FROM msdb.dbo.sysjobs;

PRINT @sql; -- << output may be truncated but EXEC will execute complete content of variable

-- uncomment to exec

There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1353009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse