C# 如何从 Windows 应用程序调用 SQL Server 代理中的作业
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9568512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to invoke the job in SQL Server agent from windows application
提问by user824910
I've scheduled a job xyzin the SQL Server Job Agent. Now I want to invoke the job from my windows application.
我已经xyz在 SQL Server 作业代理中安排了一个作业。现在我想从我的 Windows 应用程序调用作业。
采纳答案by Gats
Make a call to sp_start_job.
拨打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();
}
}
回答by Gats
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.
Agent 只处理调度和失败通知等。这有点过于简化，但 Agent 主要是一个调度程序，带有运行查询的警报。尝试编写代理作业的脚本，看看是否可以将其移动到由代理和您的应用程序运行的存储过程。
回答by Pondlife
The documentationdescribes all the options. You can use the SMO Job classfrom C# or another .NET language if you prefer not to use TSQL.
回答by Avi
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:
使用 sp_start_job 的方法有效，但它遇到了一个问题，即您不知道作业何时完成。该方法将在调用后立即返回，而不是在完成时返回。如果这对您很重要，这里有一个使用 SQL Server 管理对象 (SMO) 的函数，它只会在作业完成时返回。您需要添加对以下类的引用：
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);
}
}
}
回答by Kaarthikeyan
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/
可以在以下位置找到示例：https: //www.craftedforeveryone.com/start-stop-manage-ms-sql-server-agent-jobs-using-c-sharp/
