Mirroring Job Switcher

create procedure Mirroring_Job_Switcher
as
Begin
/*
This procedure works in conjunction with a WMI alert you will setup in SQLSEVERAGENT -> Alerts ->Mirroring Status Changed. When the status of a mirrored database changes, a job which runs this proc will be called. 
It will then deactivate or reactivate as needed, any jobs which have the
!!SAME CATEGORY AS THE DATABASE'S NAME!!.
*/
/*
This alert creation is for example only - you will need to customize your job_id number to run your Mirroring Job Switcher.

USE [msdb]
GO
/****** Object:  Alert [Mirroring Status Changed]    Script Date: 04/25/2007 10:56:51 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Mirroring Status Changed', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
		@wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE', 
		@job_id=N'YOUR UID HERE'
*/

DECLARE @JOBID UNIQUEIDENTIFIER
DECLARE @JOBS_TO_ACTIVATE CURSOR 
SET @JOBS_TO_ACTIVATE = CURSOR FAST_FORWARD FOR
--find mirrored databases which are principal - join to jobs which are not active and activate
--select j.name,j.enabled,j.job_id,db_name(database_id) as dbname, mirroring_state_desc,mirroring_role_desc 
select j.job_id
from sys.database_mirroring mirrors
,dbo.sysjobs_view j
inner join dbo.syscategories c
on j.category_id = c.category_id
where mirroring_role_desc = 'PRINCIPAL'
and c.name = db_name(mirrors.database_id)
and j.enabled = 0

OPEN @JOBS_TO_ACTIVATE
FETCH FROM @JOBS_TO_ACTIVATE INTO @JOBID
WHILE (@@FETCH_STATUS = 0)
BEGIN
	EXEC sp_update_job @job_id = @JOBID ,   @enabled = 1
   FETCH NEXT FROM @JOBS_TO_ACTIVATE INTO @JOBID
END

CLOSE @JOBS_TO_ACTIVATE
DEALLOCATE @JOBS_TO_ACTIVATE

set @JOBID = null

DECLARE @JOBS_TO_DEACTIVATE CURSOR 
SET @JOBS_TO_DEACTIVATE = CURSOR FAST_FORWARD FOR
--find mirrored databases which are mirrors - join to active jobs, to deactivate
--select j.name,j.enabled,j.job_id,db_name(database_id) as dbname, mirroring_state_desc,mirroring_role_desc 
SELECT j.job_id
from sys.database_mirroring mirrors
,dbo.sysjobs_view j
inner join dbo.syscategories c
on j.category_id = c.category_id
where mirroring_role_desc = 'MIRROR'
and c.name = db_name(mirrors.database_id)
and j.enabled = 1

OPEN @JOBS_TO_DEACTIVATE
FETCH FROM @JOBS_TO_DEACTIVATE INTO @JOBID
WHILE (@@FETCH_STATUS = 0)
BEGIN
	EXEC sp_update_job @job_id = @JOBID ,   @enabled = 0
   FETCH NEXT FROM @JOBS_TO_DEACTIVATE INTO @JOBID
END

CLOSE @JOBS_TO_DEACTIVATE
DEALLOCATE @JOBS_TO_DEACTIVATE

END