How to Identify whether SQL job is successfully executed or not in C#

Question

I have an C# method to execute a SQL job. It executes the SQL job successfully. And the code works perfect.

And I'm using standard SQL stored procedure msdb.dbo.sp_start_job for this.

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;
}

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

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.

Answer 1

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;
    }
  }
}

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

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.

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 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.

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.

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.

Answer 2

From MSDN about SqlCommand.ExecuteNonQuery Method:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

In this line:

result = sqlCommand.ExecuteNonQuery();

You want to return the number of rows affected by the command and save it to an int variable but since the type of statement is select so it returns -1. If you test it with INSERT or DELETE or UPDATE statements you will get the correct result.

By the way if you want to get the number of rows affected by the SELECT command and save it to an int variable you can try something like this:

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

And then use ExecuteScalar to get the correct result:

result = (int)execJob.ExecuteScalar();