How to execute next step of job from the first step of the same job through condition based query?


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.



Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)