SQL Server Agent Job – Using Parameter to Determine Job Flow

parametersql serversql-server-agent

Is it possible to have an Agent Job with an input parameter?

What I'm looking to do is have a flag to determine how the job flow will occur.
For example: I have a 10 step job. Steps 1-5 are always going to be executed. However, if the flag is set to 1, then then next step is going to be 6, and if the flag is not set, then execute step 8.

Can anyone please suggest how I could go about doing this?
Thanks.

Best Answer

Expanding a bit on what @Nic said in his comment. Create a quick table in an applicable database.

CREATE TABLE JobParam AS (Param1 varchar(50))

Then set it appropriately before you start your job.

UPDATE JobParam SET Param1 = 'JobPath1'

As Nic suggested I would put some code at the top of steps 6+ like such.

IF (SELECT 1 FROM JobParam WHERE Param1 = 'JobPath1')
BEGIN
    T-SQL statements
END

Another option is to put code in the job that causes a failure if the param has a specific value and use the fail/succeed logic of the job steps to branch your job.

Either way I would make sure to put in a step at the end that always runs and re-sets your value to a default in case the job is run but someone forgot to update the parameter. Maybe have the default bypass the entire job.

UPDATE JobParam SET Param1 = 'FailJob'

Related Solutions

Sql-server – How to succesfully run a batch file in an SQL Agent job

It looks like you need to configure the second step of your job to run as a domain account with permissions to write to the \KWS2-WEB-SERVER\Share\Reports\Uur share as well as read access to the C:\Reports\Energie\Uur folder.

You first will need to add a credential to SQL Server. From the Security > Credentials folder you will need to right click and choose New Credential... Fill in that information and click OK.

Once that is complete you will need to create a Proxy that uses this credential. From the SQL Server Agent > Proxies > Operating System (CmdExec) folder you will need to right click and choose New Proxy... Use the credential you created earlier.

Now you can configure your second job step to use the Proxy you just created using Run As: drop down of the job step page.

SQL Server – Bulk Change Agent Jobs to Quit on Failure

Use Script to update Job steps In Bulk

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO


    DECLARE  @job_id VARCHAR(1000),
             @Step_id INT

    DECLARE JobList CURSOR  
            FOR  

            SELECT  jb.job_ID,
                    jstp.step_id
            FROM    msdb.dbo.sysjobs jb
                    INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
            WHERE   name = 'Jobname' 
            --AND Step_name <>'Step Name'     Add Step name to filter
            ORDER BY jb.job_ID, jstp.step_id


    OPEN JobList
        FETCH NEXT FROM JobList INTO @job_id,@Step_id
        WHILE @@FETCH_STATUS = 0
            BEGIN

                     -- Execute SP sp_update_jobstep to update Job Steps and pass 2(Quit with failure) as value to parameter @on_fail_action
                    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id, @step_id = @Step_id, @on_fail_action = 2 ---- 2 Quit with failure.


                FETCH NEXT FROM JobList INTO @job_id,@Step_id
           END
        CLOSE JobList
        DEALLOCATE JobList

Note: Make sure all steps need to be updated on failure as "Quit the job reporting failure" otherwise filter them by adding in WHERE Clause

Reference Link : https://msdn.microsoft.com/en-us/library/ms189827.aspx

Best Answer

Related Solutions

Sql-server – How to succesfully run a batch file in an SQL Agent job

SQL Server – Bulk Change Agent Jobs to Quit on Failure

Related Question