如何在C#中判断SQL作业是否成功执行

Question

我有一个C#方法来执行SQL作业。它成功地执行了SQL作业。代码很完美。

我使用标准的SQL存储过程msdb.dbo.sp_start_job来实现这个目的。

以下是我的代码...

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。

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。我该如何确定作业是否成功执行。

- tarzanbappa

---

如果你在开头加上了“SET NOCOUNT ON;”，那么你的返回结果总是-1，因为你关闭了过程内有关受影响行数的信息，所以这很重要。 - msmolcic

在您的存储过程中使用输出参数和try catch来获取成功结果。 - Chandrasekar Kesavan

要监视作业状态，可以使用存储过程 MSDB.dbo.sp_help_job。例如，请参见在SQL Server 2005/2008中使用T-SQL执行和监视代理作业的方法。只需将其封装在 .NET 代码中即可。 - MicSim

@tarzanbappa...如果您可以发布您的存储过程，那么解决您的问题会更有帮助。 - Salah Akbari

我同意MicSim的观点，因为这些工作是异步的，SQL会在某个地方注册这些工作，我想应该是在sysjobhistory表中吧？所以你必须等待SQL更新工作状态为成功或失败。 - ken lacoste

Answer 1

在运行msdb.dbo.sp_start_job之后，返回代码会被映射到一个输出参数。您有机会在执行之前控制参数的名称:

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。

然而，这只是启动作业的结果，值得知道，但并不是运行作业的结果。要获取运行作业的结果，可以定期执行以下操作：

msdb.dbo.sp_help_job @jobName

该过程返回的一列是last_run_outcome，可能包含您真正感兴趣的内容。在它仍在运行时，它将为5（未知）。

作业通常是由多个步骤组成的，每个步骤根据前面步骤的结果可能会执行或不执行。另一个名为sp_help_jobhistory的过程支持许多过滤器，以指定您感兴趣的作业的特定调用和/或步骤。

SQL喜欢将作业视为计划工作，但是没有什么可以阻止您随意启动作业-尽管它确实不能为您提供与作业历史记录实例相对应的支持。日期是最好的选择（除非有人知道我不知道的技巧）。

我看到过将作业创建为临时作业，然后立即运行它，因此当前的临时执行是唯一返回的执行。但是，您最终会得到许多重复或几乎重复的作业，这些作业永远不会再次执行。如果您选择这条路，那么您将不得不计划在之后清理它们。

关于您对_sqlConnection变量的使用，请注意，您不想这样做。您的代码处置了它，但显然在调用此方法之前已经创建了它。这是不好的行为。最好在同一方法中创建连接并处置它。依靠SQL连接池使连接快速-这可能已经打开。

此外，在您发布的代码中，看起来您从execJob开始，但切换到sqlCommand，并有点混乱。我假设您始终使用execJob-并且示例反映了这一点。

- Clay

Answer 2

根据MSDN关于SqlCommand.ExecuteNonQuery方法的说明：

对于UPDATE、INSERT和DELETE语句，返回值是命令影响的行数。当在插入或更新正在进行的表上存在触发器时，返回值包括插入或更新操作以及触发器或触发器所影响的行数。对于其他类型的语句，返回值为-1。如果回滚发生，返回值也为-1。

在这一行中：

result = sqlCommand.ExecuteNonQuery();

您想要返回命令影响的行数并将其保存到int变量中，但由于语句类型为select，因此它返回-1。如果您使用INSERT或DELETE或UPDATE语句进行测试，则会得到正确的结果。

顺便说一句，如果您想要获取SELECT命令所影响的行数并将其保存到int变量中，可以尝试以下内容：

select count(*) from jobs where myjobname = @myjobname

然后使用 ExecuteScalar 获取正确的结果：

result = (int)execJob.ExecuteScalar();

- Salah Akbari

Answer 3

您需要运行存储过程 msdb.dbo.sp_help_job。

     private int CheckAgentJob(string connectionString, string jobName) {
        SqlConnection dbConnection = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand();
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.CommandText = "msdb.dbo.sp_help_job";
        command.Parameters.AddWithValue("@job_name", jobName);
        command.Connection = dbConnection;
        using (dbConnection)
        {
            dbConnection.Open();      
            using (command){
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                int status = reader.GetInt32(21); // Row 19 = Date Row 20 = Time 21 = Last_run_outcome
                reader.Close();
                return status;
            }
        }
     }

enum JobState { Failed = 0, Succeeded = 1, Retry = 2, Cancelled = 3, Unknown = 5};

在未知状态下继续轮询，直到获得答案。希望它成功了 :-)

- andrew pate