|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Yesterday @ 3:53 AM
Points: 117,
Visits: 421
|
|
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] GO EXEC msdb.dbo.sp_update_job @job_id=N'7be0b2e1-4404-4fb0-9b93-94a82f31ac5a', @notify_level_eventlog=2, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2 GO EXEC msdb.dbo.sp_attach_schedule @job_id=N'7be0b2e1-4404-4fb0-9b93-94a82f31ac5a',@schedule_id=8 GO
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!
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735,
Visits: 11,788
|
|
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; DECLARE @sql NVARCHAR(MAX); SET @sql = N'';
SELECT @sql = @sql + 'EXEC msdb.dbo.sp_update_job @job_name=N''' + name + ''', @notify_level_eventlog=2, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2; ' FROM msdb.dbo.sysjobs;
PRINT @sql; -- << output may be truncated but EXEC will execute complete content of variable
-- uncomment to exec --EXEC(@sql);
__________________________________________________________________________________________________ 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
|
|
|
|