Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the SQL Server agent to run CmdExec commands. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Requirement: call the SMS sending interface (a stored procedure) on the network server (SMS SQL Server) to send (Stage SQL Server) the data content on the Team to the mobile phone of this Team member. These two servers can be accessed locally after dialing VPN. It is required to jump to the local SQL Server and call the data interface of the two servers at the same time.

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

1. Create a SQL Server Job

2. Create a Job step that calls the operating system (CmdExec) command, and invoke the command to complete the function of automatically dialing VPN.

Codes such as:

Rasdial idc_name user_name user_password

3. Create linked servers SMS SQL Server and Stage SQL Server.

4. Create a method to read the data content that needs to be sent on Stage SQL Server, schedule the SMS sending interface of SMS SQL Server, and complete the Job step (T-SQL) of SMS sending.

5. Create a Job step of CmdExec and call the command to complete the function of automatically disconnecting VPN.

Codes such as:

Rasdial idc_name / disconnect

6. Set the sequence of the three steps (2, 4, 5). And set the scheduling frequency to 9: 00 every morning.

Two issues may be involved in this process, as follows:

1. When calling stored procedures on both servers, you may need to open a distributed transaction. The brief steps are as follows:

Run dcomcnfg commands, component Services, computer, my computer, Properties, MSDTC, Security configuration

Check "Network DTC access"

Select "allow remote clients" and "allow remote management" in client administration

Select "allow inbound", "allow outbound" and "do not require authentication" in the transaction management newsletter

Make sure that the DTC login account is: NT AuthorityNetworkService

2. The default account for performing CmdExec steps is the startup account of the SQL Server agent, usually NT AUTHORITYNetworkService, which is not a sysadmin group.

You do not have permission to execute the command. Therefore, it may be necessary to change the startup account of the agent to an authorized account.

This is the end of this article on "how to use SQL Server Agent to run CmdExec commands". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.