Click here to Skip to main content
Click here to Skip to main content

Manage SQL Server Agent Jobs using C#

, 18 Apr 2012
Rate this:
Please Sign up or sign in to vote.
C# and SQL Server Agent Jobs

Introduction

I would like introduce how to use C# to manage SQL Server Agent Jobs.

Lets try to check & wait for the job status to be idle before we fire it:

Let the job be : Some_SqlAgentJob_J250
Sql Server     : server112
Instance       : Dev
Port           : 20481

Using the code 

Let's start a new Visual Studio Project(C#).

Add the following references: 

Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum 
Microsoft.SqlServer.ConnectionInfo

Note: The references can be found in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder. It means you need to have SQL Server Installed. I have used SQL Server 2008 R2. Ref:

//Here’s the new version..

using System;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using System.Timers;

namespace Kuthuparakkal.Util
{
    class SQLJob
    {
        #region User Variables

        const int timeoutInterval = 60;//Set Timeout in seconds
        static readonly string SqlServer = @"Server112\Dev,20481"; //set SqlServer, may use instance/port too eg: USPLSVUL156\Operations,20481
        static readonly string SqlAgentJobName = "Some_SqlAgentJob_J250"; //set name of the job to fire
        
        #endregion


        static bool loopContinuity = false;
        static Timer stateTimer;
        static int CurrentRunRetryAttempt = 0;
        static ServerConnection conn;
        static Server server;
        static Job job;

        public static void Main(string[] args)
        {
            //Enable Timer
            SetTimer();
            try
            {
                conn = new ServerConnection(SqlServer); //Create SQL server conn, Windows Authentication
                server = new Server(conn); //Connect SQL Server
                job = server.JobServer.Jobs[SqlAgentJobName]; //Get the specified job
                StartJob();
            }
            catch (Exception ex)
            {
                SetTimer(true);
                Console.WriteLine("Failed to start the job :" + ex.Message);
                throw ex;
            }
            finally
            {
                Destroyobjects();
            }

        }

        static void Destroyobjects()
        {
            if (job != null)
                job = null;
            if (server != null)
                server = null;
            if (conn != null)
            {
                conn.Disconnect();
                conn = null;
            }
        }

        private static void SetTimer(bool cancel=false)
        {
            if (!cancel)//Initiate
            {
                stateTimer = new Timer(timeoutInterval * 1000);//Set Timeout interval as timeoutInterval
                stateTimer.Enabled = true;//Enable timer
                stateTimer.Elapsed += new ElapsedEventHandler(Tick); //Set timer elapsed event handler
                
            }
            else //Disable timer
            {
                if (stateTimer != null)
                    stateTimer.Dispose();

            }
        }

        static public void Tick(object source, ElapsedEventArgs e)
        {

            loopContinuity = true;//normal stop...
            Console.WriteLine(string.Format("Timeout reached at {0){1}CurrentRunRetryAttempt={2}" , e.SignalTime, Environment.NewLine, CurrentRunRetryAttempt));
            throw new Exception(string.Format("Timeout reached at {0){1}CurrentRunRetryAttempt={2}", e.SignalTime, Environment.NewLine, CurrentRunRetryAttempt));// comment this line if we do not want an abrupt stop
        }

        static void StartJob()
        {

            try
            {
                while (loopContinuity == false) //Wait till the job is idle
                {
                    job.Refresh();
                    if (job.CurrentRunStatus == JobExecutionStatus.Executing) //Check Job status and find if it’s running now
                    {
                        CurrentRunRetryAttempt++;
                        //We are not ready to fire the job
                        loopContinuity = false;
                        System.Threading.Thread.Sleep(10 * 1000); //Wait 10 secs before we proceed to check it again.
                    }
                    else
                    {
                        //We are ready to fire the job
                        loopContinuity = true; //Set loop exit
                        try
                        {
                            job.Start();//Start the job
                            SetTimer(true);//disable timer if we are able to start the job, i.e. there’s no exception on starting the job.
                        }
                        catch
                        {
                            loopContinuity = false; //Fail to start, continue to loop.
                            System.Threading.Thread.Sleep(10 * 1000); //Fail to start, wait 10 seconds and try again
                        }

                    }
                    string s = "CurrentRunStatus=" + job.CurrentRunStatus.ToString();
                    Console.WriteLine(s); //Print status             

                }
            }
            catch
            {
                throw;
            }
        }
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Kuthuparakkal
Business Analyst
United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 18 Apr 2012
Article Copyright 2012 by Kuthuparakkal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid