How do I run a SQL Server job programatically in C#?

Question

Inside SQL Server Management Studio there are more than just databases listed in the Object Explorer. Under SQL Server Agent, we have "Jobs". One of these jobs that we have updates the client to match the cache. This is important because of how our cache system works. If we make a change to the database, it is not reflected if we run this job.

So, in the C# code, there are times when I make a change to a database table that I need to run this job afterwards. It is easy to do in SQL Server Management Studio. I just right click on the job and click on "Start Job at Step..." but how would I do the same thing in C#?

The problem I have been having is not answered in what might be a duplicate post. The command line is "execute msdb.dbo.sp_start_job @job_name='Update Client Matching Cache'" and, while I have permissions to run this command from the SQL line (thus showing I have permissions), I cannot run it from the code. Passing the job name, "update client matching cache" to this procedure fails to run:

  public static void RunStoredProcedure(string strSQLJob)
    {
        SqlCommand ExecJob = new SqlCommand();
        ExecJob.CommandType = CommandType.StoredProcedure;
        ExecJob.CommandText = "msdb.dbo.sp_start_job";
        ExecJob.Parameters.AddWithValue("@job_name", strSQLJob);

    using (SqlConnection sc = DatabaseManager.SqlConnection())
    {
        sc.Open();
        using (ExecJob)
        {
            ExecJob.Connection = sc;
            ExecJob.ExecuteNonQuery();
        }
    }
}

The error is: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

I also get the same error if I open Visual Studio in admin mode.

So I googled.. *tsql start job* and came across [sp_start_job](https://msdn.microsoft.com/en-us/library/ms186757.aspx). This would basically start a job... I'm not sure why this is even a question. — Erik Philips, Jun 02 '16 at 20:16
If that is a sql command, it fails in a query. The Error is: "Could not find stored procedure 'sp_start_job'" — xarzu, Jun 02 '16 at 20:24
Try `EXECUTE msdb.dbo.sp_start_job @job_name = '[JOB NAME HERE]'` — Igor, Jun 02 '16 at 20:26
Igor, I cam run this from the sql query command line, but I get a permissions violation in C# — xarzu, Jun 03 '16 at 18:08

score 3 · Answer 1 · answered Jun 02 '16 at 20:27

You need to use Microsoft.SqlServer.Management.Smo.Agent namespace..In this namespace you have job class which has INVOKEmethod.Here is some sample code from MSDN..

Server srv = new Server("(local)");
Job jb = new Job(srv.JobServer, "Test Job");
jb.Create();
JobStep jbstp = new JobStep(jb, "Test Job Step");
jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;
jbstp.Create();
jb.ApplyToTargetServer(srv);
jb.IsEnabled = true;
jb.Invoke();

The reference, by the way, is of the file Microsoft.SqlServer.Smo.dll — xarzu, Jun 03 '16 at 18:22

score 0 · Answer 2 · edited Jun 02 '16 at 22:28

0

You can create a new job and a new "Task" table.

The new job would be checking the status of the "Task" table every 5 minutes or so. If there is a new row in that table with a status, for example: "pending", then, this job will start the target job, and will change the status to "in progress".

Finally, the last job can include a step to change the status again to "success" or "failure" when it's finished.

edited Jun 02 '16 at 22:28

Tim Malone

3,364
5
37
50

answered Jun 02 '16 at 21:20

user3470151

1
1

Thank you Tim. It looks much better now. – user3470151 Jun 04 '16 at 13:27

score 0 · Answer 3 · edited May 23 '17 at 12:31

0

Using the answer in a similar question almost works apart from a privilege error being thrown How to invoke the job in SQL Server agent from windows application

edited May 23 '17 at 12:31

Community

1
1

answered Jun 02 '16 at 22:57

xarzu

8,657
40
108
160

How do I run a SQL Server job programatically in C#?

3 Answers3