Control the status of step in SQL SERVER agent JOBS
Par Draggi le vendredi, novembre 16 2012, 13:20 - SQL - Lien permanent
When you try to create a job that will run based on the online status of a database, for example, on SQL server mirroring environment. Here 's how to check the status of the mirroring server before executing next step of your job.
Add step 1 in your failed jobs with this command:
DECLARE @ServerState VARCHAR(20)
SELECT @ServerState=mirroring_role_desc
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where mirroring_state_desc is not null
IF @ServerState='PRINCIPAL'
BEGIN
PRINT 1;
END
ELSE
RAISERROR('This server is the Slave and the database is offline.', 11, 1);
Set the properties of the step 1 to be success, go to next step on success ...