CODEWITHSUNDEEP
c#sql-serverwinforms
user824910
user824910

Reputation: 1117

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

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

Upvotes: 24

Views: 42001

Answers (6)

Věroslav Olič
Věroslav Olič

Reputation: 1

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.

Upvotes: 0

Kaarthikeyan
Kaarthikeyan

Reputation: 570

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/

Upvotes: 1

Avi
Avi

Reputation: 972

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);
        }
    }
}

Upvotes: 5

user596075
user596075

Reputation:

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();
    }
}

Upvotes: 35

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 2

Gats
Gats

Reputation: 3462

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.

Upvotes: 2

Related Questions