August 14, 2008 at 2:39 pm
Does anyone have a script to configure all jobs in a SQL instance to write to the Event Log upon failure?
I don't want to have to do this from the GUI...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 15, 2008 at 2:24 am
Saw an exact question / solution 2 months ago. Here's the script, but I can't give you the author's name:
SET NOCOUNT ON
SELECT IDENTITY(int, 1, 1) AS agentJobId, name AS agentJobName
INTO #agentJob
FROM msdb.dbo.sysjobs
ORDER BY name
DECLARE @agentJobName sysname, @agentJobId int, @job_id uniqueidentifier
SET @agentJobId = 1
SELECT @agentJobName = agentJobName
FROM #agentJob
WHERE agentJobId = @agentJobId
WHILE @@ROWCOUNT <> 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @agentJobName OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @notify_level_eventlog = 2
SELECT @agentJobId = @agentJobId + 1, @job_id = NULL
SELECT @agentJobName = agentJobName
FROM #agentJob
WHERE agentJobId = @agentJobId
END
DROP TABLE #agentJob
Wilfred
The best things in life are the simple things
August 15, 2008 at 4:44 am
Thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply