How to invoke the job in SQL Server agent from windows application
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();
}
}
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.
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.
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);
}
}
}