How to invoke the job in SQL Server agent from windows application

Question

I've scheduled a job xyz in the SQL Server Job Agent. Now I want to invoke the job from my windows application.

score 34 · Accepted Answer · edited Sep 09 '15 at 19:38

Make a call to sp_start_job.

exec msdb.dbo.sp_start_job @job_name = 'YourJobName'

MSDN Reference on sp_start_job

SqlConnection DbConn = new SqlConnection(YourConnectionString);
SqlCommand ExecJob = new SqlCommand();
ExecJob.CommandType = CommandType.StoredProcedure;
ExecJob.CommandText = "msdb.dbo.sp_start_job";
ExecJob.Parameters.AddWithValue("@job_name", "YourJobName")
ExecJob.Connection = DbConn; //assign the connection to the command.

using (DbConn)
{
    DbConn.Open();
    using (ExecJob)
    {
        ExecJob.ExecuteNonQuery();
    }
}

score 5 · Answer 2 · answered Jan 04 '19 at 01:08

The method of using sp_start_job works, but it runs into a problem in that you don't know exactly when the job finished. The method will return as soon as it's called, not when it's completed. If that's important to you, here's a function using the SQL Server Management Objects (SMO) which will only return when the job is completed. You'll need to add references to the following classes:

Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum 
Microsoft.SqlServer.ConnectionInfo

Here's the code:

Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent

public void RunSQLAgentJob(string JobName)
{
    SqlConnection DbConn = new SqlConnection(connectionstring);
    ServerConnection conn;
    Job job;
    Server server;

    using (DbConn) {
        conn = new ServerConnection(DbConn);
        server = new Server(conn);
        job = server.JobServer.Jobs(JobName);
        // make sure it's not already running before starting it
        if (job.CurrentRunStatus == JobExecutionStatus.Idle) 
            job.Start();
        while (job.CurrentRunStatus == JobExecutionStatus.Executing) {
            job.Refresh();
            Console.WriteLine($"Current status of {JobName} is {job.CurrentRunStatus.ToString}");
            System.Threading.Thread.Sleep(3000);
        }
    }
}

What language is that? Looks almost identical to C# but C# doesn't use "imports" and Jobs() is not a function. — NickG, Dec 20 '19 at 11:27
I guess it's a mix of C# and VB.Net. I'm a VB.Net programmer, and had the code in that language, but since the question was asked with the C# tag, I tried converting it to C#, but apparently, didn't get it all right. Instead of "Imports", I should have written "using". — Avi, Dec 21 '19 at 16:57
Using System.Data.SqlClient caused an error for me, I had to go for using Microsoft.Data.SqlClient; instead — Andreas, Dec 29 '22 at 08:33
Also job = server.JobServer.Jobs(JobName) needs to be job = server.JobServer.Jobs[JobName] instead to work properly, because it's an array and not a method — Andreas, Dec 29 '22 at 08:42

score 2 · Answer 3 · answered Mar 05 '12 at 14:44

2

Agent jobs are generally just scripts that run queries anyway. Is there any reason you can't just run the query that the agent job is running anyway?

Agent just handles the scheduling and failure notifications etc. This is a bit of an over simplification, but Agent is mostly a scheduler with alerts that runs queries. Try scripting out your agent job and see if it's something you can move to a stored procedure that is run by both agent and your app.

answered Mar 05 '12 at 14:44

Gats

3,452
19
20

In my case i know the job id.. i want that job scheduled to be changed with the help of windows application.. – user824910 Mar 05 '12 at 14:48
MSDB has most of the data agent uses. Take a look around there and, while I've not done it myself, I'm sure you can change it by updating it directly. This can be a little dangerous however, and it seems to me that a windows service would be a better solution for what you're trying to do. Your OP said you wanted to invokve it, but you're now saying you want to change it.. is that correct? – Gats Mar 05 '12 at 15:01
A main reason for this is, the Process may take a long time to compute. By default the SQL CommandTimeout is only 30 Seconds and after that time expires, the connection is severed. You could lengthen the Timeout to persist the Connection longer, maybe even make it so the call is Asyncronous, but you will still run into issues where the Job may take 30 minutes or so and you wouldn't want to maintain a connection for that long - especially from a Web Server, where you want to give your users the ability to Manually Kick off a Job Process. That is why a feature like this is necessary. – MikeTeeVee Jun 20 '19 at 11:43

score 2 · Answer 4 · answered Mar 05 '12 at 15:41

2

The documentation describes all the options. You can use the SMO Job class from C# or another .NET language if you prefer not to use TSQL.

answered Mar 05 '12 at 15:41

Pondlife

15,992
6
37
51

Thanks. Do you know if this uses WMI in the background? – tom redfern Jan 13 '15 at 11:39
@TomRedfern SMO does [wrap WMI](http://msdn.microsoft.com/en-us/library/ms162557.aspx) for some operations (like working with the SQL Server services), but for many it just generates TSQL and sends it to the server. – Pondlife Jan 14 '15 at 17:35

Kaarthikeyan · Answer 5 · 2019-01-25T08:05:53.270

1

using Microsoft.SqlServer.Management.Smo;

Server server = new Server("your_server_address");
server.JobServer.Jobs["job_name"]?.Start();

examples can be found at : https://www.craftedforeveryone.com/start-stop-manage-ms-sql-server-agent-jobs-using-c-sharp/

edited Jan 25 '19 at 08:05

answered Jan 18 '19 at 13:20

Kaarthikeyan

500
5
12

score 0 · Answer 6 · edited Sep 23 '21 at 09:33

in your code - this part:

while (job.CurrentRunStatus == JobExecutionStatus.Executing) {
    job.Refresh();

leads in my case to not fulfilled while-loop condition, even the SQL job was actually started in the previous step. Wouldn't be better to change the condition to something similar to below?

 while (job.CurrentRunStatus != JobExecutionStatus.Idle)

There are many statuses the job can be in (BetweenRetries, Executing, Idle, PerformingCompletionAction, Suspended, WaitingForStepToFinish, WaitingForWorkerThread) and I suspect that is the reason in my case.

How to invoke the job in SQL Server agent from windows application

6 Answers6

Linked

Related