here is a handy guide to help you add  SQL Server Agent cmdexec jobstep with ease. Our SQL Server Support team is here to lend a hand with your queries and issues.

How to add a SQL Server Agent cmdexec Jobstep

SQL Server Agent can be described as a part of Microsoft SQL Server that helps schedule as well as automate administrative tasks, like indexing, backups, and data import or export.

In fact, one of the key steps used in SQL Server Agent is the “cmdexec” step. It allows us to run command-prompt executables like batch files, VBScript files, Jscript files, and so on as a job step.

According to our experts, automotive tasks like running backups, running scripts, or even calling other executable programs are as easy as 1-2-3 with cmdexec.

Let’s take a look at this process in detail:

1. First head to Object Explorer and then we have to connect to an instance of the SQL Server Database Engine. After that, we have to expand this instance.
2. Next, we have to expand SQL Server Agent and create a new job. We can also modify an existing job.
3. Then right-click the job and choose Properties.
4. After that, click the Steps page, and then select New.
5. Next, enter a job Step name in the New Job Step box and choose Operating System(cmdexec) from the Type list.
6. Then, select the proxy account with the credentials to be used by the job in the Run as list option.
7. Next, enter a value between 0 to 999999 in the Process exit code of a successful command box.
8. At this point, enter the operating system command or executable program in the Command box.
9. Finally, we have to head to the Advanced page in order to set job step options.

   This includes options like what step to take if the job step succeeds or fails, how many times the job step has to be executed by the SQL Server Agent, and so on.

How to create a CmdExec job step via Transact-SQL

Alternatively, we can create a CmdExec job step via Transact-SQL:

1. First, we have to connect to an instance of Database Engine in the Object Explorer.
2. Then, we have to New Query on the Standard bar.
3. Next, add the following example in the query window and click Execute:

   -- creates a job step that uses CmdExec
USE msdb;
GO
EXEC sp_add_jobstep
@job_name = N'Monthly Payslip Data Backup',
@step_name = N'Set database to read only',
@subsystem = N'CMDEXEC',
@command = 'C:\clickme_scripts\SQL11\PostBOLReorg GetHsX.exe',
@retry_attempts = 3,
@retry_interval = 3 ;
GO


[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To sum up, our Support Engineers demonstrated how to add a SQL Server Agent cmdexec job step with ease.