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 变量的说明.你不想那样做.您的代码处理了它，但它显然是在调用此方法之前在其他地方创建的.那是坏枣.您最好只创建连接并使用相同的方法处理它.依靠 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屋！
