Aller au contenu | Aller au menu | Aller à la recherche

Control the status of step in SQL SERVER agent JOBS

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 ...

Ajouter un commentaire

Les commentaires peuvent être formatés en utilisant une syntaxe wiki simplifiée.

Fil des commentaires de ce billet