To run the CmdExec command using SQL Server Agent

Requirements: The SMS Send interface (a stored procedure) on the network server (SMS SQL Server) is sent to send the data content on the Stage SQL Server to the mobile phone of the team member. The two servers, which are locally accessible after the VPN is dialed. A jump is required on the local SQL Server, while the data interface for both servers is called.

Implementation methods and steps (in the local SQL Server environment):

1. Create a SQL Server Job

2. Create a job step that invokes the operating system (CmdExec) command to invoke the command to complete the automatic dial VPN function.

Code such as:

rasdial idc_name user_name User_password

3, create the linked server SMS SQL Server and stage SQL Server.

4, create a read stage SQL Server needs to send the data content, and dispatch SMS SQL Server SMS send interface, complete SMS send job step (t-SQL).

5, create a CmdExec job step, invoke the command to complete the automatic disconnect VPN function.

Code such as:

Rasdial Idc_name/disconnect

6, set the order of the three steps (2,4,5). and set the dispatch frequency to 9 o ' clock every morning.

The process may involve two issues, as follows:

1. Call a stored procedure on two separate servers, and you may need to open a distributed transaction. The brief steps are as follows:

Run DCOMCNFG command, Component Services, computer, my Computer, attributes, MSDTC, security Configuration

Select Network DTC Access

Select Allow remote client to allow remote administration in client management

"Allow inbound" "Allow outbound" is not required for authentication in transaction management communications

Guarantee DTC Login account as: NT AUTHORITY\NetworkService

2, the implementation of the CmdExec step of the default account is the SQL Server Agent startup account, is generally NT AUTHORITY\NetworkService, the account is not a sysadmin group,

You do not have permission to execute this command. Therefore, you may need to modify the agent's startup account as a privileged account.

Summary: The key point of this paper is to grasp the correct opening of distributed transactions and SQL Server Agent, usually the job account AUTHORITY\NetworkService does not belong to the System Administrators group, the agent performs some tasks failed, Note that the permissions required to perform the operation, such as the execution of the CmdExec command in this article, require the privileges of the sysadmin or operating system administrator.