In this article, i will show you how you can start and stop sql jobs running on remote sql server using C#.
First of all, Create a console application for demonstration of this article in visual studio.
Add references to the following dlls in your project using Visual studio's Add reference dialog box.
1) Microsoft.SqlServer.ConnectionInfo
2) Microsoft.SqlServer.Smo
3) Microsoft.SqlServer.SqlEnum
Now write the below code to start or stop the SQL job.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| static void StartStopSqlJob(string jobName) { //Sql server name string sqlServerName = "192.168.206.52"; //Sql server user name and password that have access to sql agent string sqlUserName = "sa"; string sqluserPassword = "password"; //Create a connection to the Sql Server Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlServerName, sqlUserName, sqluserPassword); //Get an instance of the Sql Server Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(connection); try { //Get the particular job object using job name Microsoft.SqlServer.Management.Smo.Agent.Job job = server.JobServer.Jobs[jobName]; //Check the job state, if it is not running i.e Idle then start the job if (job.CurrentRunStatus == Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus.Idle) job.Start(); if (job.CurrentRunStatus == Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus.Executing) job.Stop(); } catch (Exception ex) { //Log the exception Console.WriteLine(ex.StackTrace); } } |
The method takes jobName as its argument. It then get an instance of the sql server on which you want to run or stop the job, finds the job by its name. Then it checks the status of the job, if it is currently idle then the code starts the job or stops the job if it in running state.
