如何识别是否成功执行或没有在C＃中的SQL任务 [英] How to Identify whether SQL job is successfully executed or not in C#
问题描述
我有一个C＃方法执行一个SQL作业。它成功地执行SQL作业。
而code运行完美。
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.
下面是我的code ..
Here is my code..
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;
}
下面是该作业
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 返回code被映射到一个输出参数。你有机会来控制参数的名称的之前执行的：
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;
}
}
}
您需要知道返回code做到这一点的数据类型 - 和 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.
一个作业通常是多个步骤 - 其中，每个步骤可以或可以不根据previous步骤的结果执行。 sp_help_jobhistory 称为另一个过程支持很多的过滤器，以指定特定的调用（S）和/或你感兴趣的工作的步骤。
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 变量的音符。你不想这样做。您code作主的，但它显然已经创建，此方法被调用之前。这是坏朱朱。你刚才关创建连接，它处理同样的方法更好。依靠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.
此外 - 在code你贴 - 它看起来像你开始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屋！
