2

While searching on above topic in the internet, I found two approaches,Both are working fine, But I need to know the difference between the two, Which one is suitable for what occasion etc... Our Jobs take some time and I need a way to wait till the Job finishes before the next C# line executes.

Approach One

        var dbConn = new SqlConnection(myConString);
        var execJob = new SqlCommand
        {
            CommandType = CommandType.StoredProcedure,
            CommandText = "msdb.dbo.sp_start_job"
        };
        execJob.Parameters.AddWithValue("@job_name", p0);
        execJob.Connection = dbConn;

        using (dbConn)
        {
            dbConn.Open();
            using (execJob)
            {
                execJob.ExecuteNonQuery();
                Thread.Sleep(5000);
            }
        }

Approach Two

        using System.Threading;
        using Microsoft.SqlServer.Management.Smo;
        using Microsoft.SqlServer.Management.Smo.Agent;

        var server = new Server(@"localhost\myinstance");
        var isStopped = false;
        try
        {
            server.ConnectionContext.LoginSecure = true;
            server.ConnectionContext.Connect();
            var job = server.JobServer.Jobs[jobName];
            job.Start();
            Thread.Sleep(1000);
            job.Refresh();
            while (job.CurrentRunStatus == JobExecutionStatus.Executing)
            {
                Thread.Sleep(1000);
                job.Refresh();
            }
            isStopped = true;
        }
        finally
        {
            if (server.ConnectionContext.IsOpen)
            {
                server.ConnectionContext.Disconnect();
            }
        }
Rasika
  • 312
  • 1
  • 7
  • 19
  • so reading the code @Rasika, what don't you understand.. do you know what the code is doing..? it's pretty straight forward.. – MethodMan Nov 05 '15 at 23:09
  • Of course, First one uses "sp_start_job" and second one is not. May be there are pros and cons on that. Its two different approaches to do the same thing using different libraries. I want to know Which one is suitable for what occasion etc... may be the second approach is the latest , that's why asked. – Rasika Nov 05 '15 at 23:21
  • then look at `MSDN sp_start_job` also looks like a sql agent is running the second process vs the 1st one where you are actually explicitly calling a Sql stored procedure via C# code only thing I can see is what if one or the other is down.. then that's the only way in my opinion you will be able to tell which is better also do some profiling to see which one runs quicker and with less resources.. – MethodMan Nov 05 '15 at 23:24

1 Answers1

5

sp_start_job - sample 1

Your first example calls your job via the sp_start_job system stored procedure. Note that it kicks off the job asynchronously, and the thread sleeps for an arbitrary period of time (5 seconds) before continuing regardless of the job's success or failure.

SQL Server Management Objects (SMO) - sample 2

Your second example uses (and therefore has a dependency on) the SQL Server Management Objects to achieve the same goal.

In the second case, the job also commences running asynchronously, but the subsequent loop watches the Job Status until it is not longer Executing. Note that the "isStopped" flag appears to serve no purpose, and the loop could be refactored somewhat as:

        job.Start();
        do
        {
            Thread.Sleep(1000);
            job.Refresh();
        } while (job.CurrentRunStatus == JobExecutionStatus.Executing);

You'd probably want to add a break-out of that loop after a certain period of time.

Other Considerations

It seems the same permissions are required by each of your examples; essentially the solution using SMO is a wrapper around sp_start_job, but provides you with (arguably) more robust code which has a clearer purpose.

Use whichever suits you best, or do some profiling and pick the most efficient if performance is a concern.

primalBlaze
  • 86
  • 3