Scenario: I came across a situation like job to execute the package based on the conditional check of the database configured in mirror configuration, if it is not configured, second step needs to be run else the job completed with first step output.
Solution: Put this below query in the first step and change the first step of job On Success and On Failure properties as per the below screenshot.
|
DECLARE @Mirrorstatus int
select
@Mirrorstatus=
COUNT(a.name )
FROM
sys.databases A
INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
and B.mirroring_state is NULL
WHERE a.name='Database_Name'
if @Mirrorstatus<=0
BEGIN
RAISERROR('50005 A mirroring is currently running.',16, -1,
@@servername)
--EXEC msdb.DBO.sp_stop_job @job_name='Package_name_'
END
ELSE
BEGIN
select @@SERVERNAME
END
|
The first step of the job will check the database exists in mirroring configuration and based on the success result then second job step will execute.Refer the below figure.
No comments:
Post a Comment