Skip to content

create-a-cmdexec-job-step.md

Latest commit

 

History

History
86 lines (60 loc) · 4.51 KB

create-a-cmdexec-job-step.md

File metadata and controls

86 lines (60 loc) · 4.51 KB
title Create a CmdExec Job Step
ms.custom seo-lt-2019
ms.date 01/19/2017
ms.prod sql
ms.prod_service sql-tools
ms.technology ssms
ms.topic conceptual
helpviewer_keywords
CmdExec jobs
ms.assetid b48da5b4-6fe7-4eb7-bade-dc7d697c6d5c
author markingmyname
ms.author maghan
ms.manager jroth
ms.reviewer
monikerRange = azuresqldb-mi-current || >= sql-server-2016 || = sqlallproducts-allversions

Create a CmdExec Job Step

[!INCLUDEappliesto-ss-asdbmi-xxxx-xxx-md]

Important

On Azure SQL Database Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details.

This topic describes how to create and define a [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Agent job step in [!INCLUDEssCurrent] that uses an executable program or operating system command by using [!INCLUDEssManStudioFull], [!INCLUDEtsql] or SQL Server Management Objects.

Before You Begin

Security

By default, only members of the sysadmin fixed server role can create CmdExec job steps. These job steps run under the context of the SQL Server Agent service account unless the sysadmin user creates a proxy account. Users who are not members of the sysadmin role can create CmdExec job steps if they have access to a CmdExec proxy account.

Permissions

For detailed information, see Implement SQL Server Agent Security.

Using SQL Server Management Studio

To create a CmdExec job step

  1. In Object Explorer, connect to an instance of the [!INCLUDEssDEnoversion], and then expand that instance.

  2. Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties.

  3. In the Job Properties dialog, click the Steps page, and then click New.

  4. In the New Job Step dialog, type a job Step name.

  5. In the Type list, choose Operating system (CmdExec).

  6. In Run as list, select the proxy account with the credentials that the job will use. By default, CmdExec job steps run under the context of the SQL Server Agent service account.

  7. In the Process exit code of a successful command box, enter a value from 0 to 999999.

  8. In the Command box, enter the operating system command or executable program. See "Using Transact T-SQL for an example.

  9. Click the Advanced page to set job step options, such as: what action to take if the job step succeeds or fails, how many times [!INCLUDEssNoVersion] Agent should try to execute the job step, and the file where [!INCLUDEssNoVersion] Agent can write the job step output. Only members of the sysadmin fixed server role can write job step output to an operating system file.

Using Transact-SQL

To create a CmdExec job step

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

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

For more information, see sp_add_jobstep (Transact-SQL)

Using SQL Server Management Objects

To create a CmdExec job step

Use the JobStep class by using a programming language that you choose, such as Visual Basic, Visual C#, or PowerShell.