How to determine if a SQL job succeeded or not in C #
I have a C # method to do a SQL job. It completes the SQL job successfully. And the code works fine.
And for that I am using a standard SQL stored procedure msdb.dbo.sp_start_job
.
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 doing 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 that even the job is successful or not, it always returns -1 . How to determine if a job has completed successfully or not.
source to share
After startup, msdb.dbo.sp_start_job
the return code is mapped to the output parameter. You have the ability to control the parameter name before 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;
}
}
}
To do this, you need to know the data type of the return code, and for sp_start_job
- SqlDbType.Int
.
However, these are only the results of running the task, which is worth knowing, but not the result of your work. To get the results of a task, you can periodically run:
msdb.dbo.sp_help_job @jobName
One of the columns returned by the procedure - last_run_outcome
and probably contains what you are really interested in. It will be 5 (unknown) while it still works.
An assignment usually consists of several steps - each step may or may not be performed according to the results of the previous steps. Another procedure called sp_help_jobhistory
supports many filters to specify which specific calls and / or steps of the job you are interested in.
SQL likes to think of work as planned work, but nothing prevents you from just getting started ad-hoc - although it doesn't really give you much support for correlating your ad work with an instance. work history. The dates are about as good as anything (unless someone knows a trick that I don't know).
I've seen where a job is created by an ad-hoc job just before it starts, so the current ad-hoc execution is the only execution executed. But you end up with a lot of duplicate or nearly duplicated jobs that will never run again. Something you will have to plan after cleaning if you go this route.
A note on using a variable _sqlConnection
. You don't want to do this. Your code is in charge of it, but it appears to have been created somewhere before this method was called. This is a bad jui. You'd better just create a connection and get rid of it using the same method. Rely on SQL connection pooling to quickly establish a connection that is probably already on.
Also - in the code you posted it looks like you started with execJob, but switched to sqlCommand - and kind of messed up editing. I assumed that you used execJob all the time - and this is reflected in the example.
source to share
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 an inserted or updated table, the return value includes the number of rows affected by both insert and update, as well as the number of rows affected by the trigger or triggers. For all other operator types, the return value is -1. If a rollback occurs, the return value is -1.
In this line:
result = sqlCommand.ExecuteNonQuery();
You want to return the number of rows affected by this command and store them in an int variable, but since the type of the operator select
, it returns -1
. If you test it with the operators INSERT
or DELETE
or UPDATE
, you will get the correct result.
By the way, if you want to get the number of lines affected by the command select
and store them in 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();
source to share
You need to run the saved version of 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};
Continue polling on Unknown until you get an answer. Hope it works :-)
source to share