如何在 C# 中识别 SQL 作业是否成功执行 [英] How to Identify whether SQL job is successfully executed or not in C#
问题描述
我有一个 C# 方法来执行 SQL 作业.它成功地执行了 SQL 作业.并且代码运行良好.
I have an C# method to execute a SQL job. It executes the SQL job successfully. And the code works perfect.
我为此使用标准 SQL 存储过程 msdb.dbo.sp_start_job.
And I'm using standard SQL stored procedure msdb.dbo.sp_start_job for this.
这是我的代码..
public int ExcecuteNonquery()
{
var result = 0;
using (var execJob =new SqlCommand())
{
execJob.CommandType = CommandType.StoredProcedure;
execJob.CommandText = "msdb.dbo.sp_start_job";
execJob.Parameters.AddWithValue("@job_name", "myjobname");
using (_sqlConnection)
{
if (_sqlConnection.State == ConnectionState.Closed)
_sqlConnection.Open();
sqlCommand.Connection = _sqlConnection;
result = sqlCommand.ExecuteNonQuery();
if (_sqlConnection.State == ConnectionState.Open)
_sqlConnection.Close();
}
}
return result;
}
这是在作业中执行的 sp
Here is the sp which executing inside the job
ALTER PROCEDURE [Area1].[Transformation]
AS
BEGIN
SET NOCOUNT ON;
SELECT NEXT VALUE FOR SQ_COMMON
-- Transform Master Data
exec [dbo].[sp_Transform_Address];
exec [dbo].[sp_Transform_Location];
exec [dbo].[sp_Transform_Product];
exec [dbo].[sp_Transform_Supplier];
exec [dbo].[sp_Transform_SupplierLocation];
-- Generate Hierarchies and Product References
exec [dbo].[sp_Generate_HierarchyObject] 'Area1',FGDemand,1;
exec [dbo].[sp_Generate_HierarchyObject] 'Area1',RMDemand,2;
exec [dbo].[sp_Generate_Hierarchy] 'Area1',FGDemand,1;
exec [dbo].[sp_Generate_Hierarchy] 'Area1',RMDemand,2;
exec [dbo].[sp_Generate_ProductReference] 'Area1',FGDemand,1;
exec [dbo].[sp_Generate_ProductReference] 'Area1',RMDemand,2;
-- Transform Demand Allocation BOM
exec [Area1].[sp_Transform_FGDemand];
exec [Area1].[sp_Transform_FGAllocation];
exec [Area1].[sp_Transform_RMDemand];
exec [Area1].[sp_Transform_RMAllocation];
exec [Area1].[sp_Transform_BOM];
exec [Area1].[sp_Transform_RMDemand_FK];
-- Transform Purchasing Document Data
exec [dbo].[sp_Transform_PurchasingDoc];
exec [dbo].[sp_Transform_PurchasingItem];
exec [dbo].[sp_Transform_ScheduleLine];
exec [dbo].[sp_CalculateRequirement] 'Area1'
exec [dbo].[sp_Create_TransformationSummary] 'Area1'
-- Trauncate Integration Tables
exec [dbo].[sp_TruncateIntegrationTables] 'Area1'
END
问题是，即使作业执行成功与否，它总是返回-1.如何确定作业是否成功执行.
The problem is, even the job is executed successfully or not it always returns -1. How can I identify whether job is successfully executed or not.
推荐答案
运行 msdb.dbo.sp_start_job 后，返回码映射到输出参数.您有机会控制参数的名称在执行之前:
After running msdb.dbo.sp_start_job the return code is mapped to an output parameter. You have the opportunity to control the parameter's name prior to execution:
public int StartMyJob( string connectionString )
{
using (var sqlConnection = new SqlConnection( connectionString ) )
{
sqlConnection.Open( );
using (var execJob = sqlConnection.CreateCommand( ) )
{
execJob.CommandType = CommandType.StoredProcedure;
execJob.CommandText = "msdb.dbo.sp_start_job";
execJob.Parameters.AddWithValue("@job_name", "myjobname");
execJob.Parameters.Add( "@results", SqlDbType.Int ).Direction = ParameterDirection.ReturnValue;
execJob.ExecuteNonQuery();
return ( int ) sqlCommand.Parameters["results"].Value;
}
}
}
您需要知道返回代码的数据类型才能执行此操作 - 对于 sp_start_job，它是 SqlDbType.Int.
You need to know the datatype of the return code to do this - and for sp_start_job, it's SqlDbType.Int.
然而，这只是开始工作的结果，值得了解，而不是运行你的工作的结果.要获得作业的运行结果，您可以定期执行:
However, this is only the results of starting the job, which is worth knowing, but isn't the results of running your job. To get the results running of your job, you can periodically execute:
msdb.dbo.sp_help_job @jobName
该过程返回的一列是 last_run_outcome，它可能包含您真正感兴趣的内容.它仍在运行时将是 5(未知).
One of the columns returned by the procedure is last_run_outcome and probably contains what you're really interested in. It will be 5 (unknown) while it's still running.
作业通常是多个步骤 - 每个步骤可能会或可能不会根据先前步骤的结果执行.另一个名为 sp_help_jobhistory 的过程支持许多过滤器来指定您感兴趣的工作的特定调用和/或步骤.
A job is usually the a number of steps - where each step may or may not be executed according to the outcome of previous steps. Another procedure called sp_help_jobhistory supports a lot of filters to specify which specific invocation(s) and/or steps of the job you're interested in.
SQL 喜欢将作业视为计划工作 - 但没有什么可以阻止您刚刚开始临时作业 - 尽管它并没有真正为您提供太多支持来将临时作业与实例关联起来工作经历.日期几乎是最好的(除非有人知道我不知道的技巧.)
SQL likes to think about jobs as scheduled work - but there's nothing to keep you from just starting a job ad-hoc - although it doesn't really provide you with much support to correlate your ad-hoc job with an instance is the job history. Dates are about as good as it gets (unless somebody knows a trick I don't know.)
我已经在运行它之前看到了创建临时作业的位置，因此当前的临时执行是唯一返回的执行.但是你最终会得到很多重复或几乎重复的工作，这些工作永远不会再被执行.如果你走那条路，你必须计划事后清理.
I've seen where the job is created ad-hoc job just prior to running it, so the current ad-hoc execution is the only execution returned. But you end up with a lot of duplicate or near-duplicate jobs laying around that are never going to be executed again. Something you'll have to plan on cleaning up afterwards, if you go that route.
关于您使用 _sqlConnection 变量的说明.你不想那样做.您的代码处理了它，但它显然是在调用此方法之前在其他地方创建的.那是糟糕的juju.您最好只创建连接并以相同的方法处理它.依靠 SQL 连接池来加快连接速度——这可能已经打开了.
A note on your use of the _sqlConnection variable. You don't want to do that. Your code disposes of it, but it was apparently created elsewhere before this method gets called. That's bad juju. You're better off just creating the connection and disposing of it the same method. Rely on SQL connection pooling to make the connection fast - which is probably already turned on.
另外 - 在您发布的代码中 - 看起来您从 execJob 开始但切换到 sqlCommand - 并且有点搞砸了编辑.我以为您一直指的是 execJob - 这反映在示例中.
Also - in the code you posted - it looks like you started with execJob but switched to sqlCommand - and kinda messed up the edit. I assumed you meant execJob all the way through - and that's reflected in the example.
这篇关于如何在 C# 中识别 SQL 作业是否成功执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
