locked
SQL Agent Jobs C# RRS feed

  • Question

  • I am trying to Invoke a SQL agent job from my C# Application.

    I want to be able to.

     - On the Press of a button be able to Execute the Job.

    But i also want to have some sort of message for when the job is finished running or fails.


    The Below Code sample from another Answer will not work for me as i need access to the local MSDB database.


    private Dictionary<int, string> ExecutionStatusDictionary = new Dictionary<int, string>()
    {
        {0, "Not idle or suspended"},
        {1, "Executing"},
        {2, "Waiting for thread"},
        {3, "Between retries"},
        {4, "Idle"},
        {5, "Suspended"},
        {7, "Performing completion actions"}
    };
    
    public string GetStatus()
    {
        SqlConnection msdbConnection = new SqlConnection("Data Source=GACDTL01CR585M;Initial Catalog=msdb;Integrated Security=SSPI");
        System.Text.StringBuilder resultBuilder = new System.Text.StringBuilder();
    
        try
        {
            msdbConnection.Open();
    
            SqlCommand jobStatusCommand = msdbConnection.CreateCommand();
    
            jobStatusCommand.CommandType = CommandType.StoredProcedure;
            jobStatusCommand.CommandText = "sp_help_job";
    
            SqlParameter jobName = jobStatusCommand.Parameters.Add("@job_name", SqlDbType.VarChar);
            jobName.Direction = ParameterDirection.Input;
            jobName.Value = "LoadRegions";
    
            SqlParameter jobAspect = jobStatusCommand.Parameters.Add("@job_aspect", SqlDbType.VarChar);
            jobAspect.Direction = ParameterDirection.Input;
            jobAspect.Value = "JOB";
    
            SqlDataReader jobStatusReader = jobStatusCommand.ExecuteReader();
    
            while (jobStatusReader.Read())
            {
                resultBuilder.Append(string.Format("{0} {1}",
                    jobStatusReader["name"].ToString(),
                    ExecutionStatusDictionary[(int)jobStatusReader["current_execution_status"]]
                ));
            }
            jobStatusReader.Close();
        }
        finally
        {
            msdbConnection.Close();
        }
    
        return resultBuilder.ToString();
    }


    Tuesday, January 1, 2013 9:57 PM

Answers

  • Thanks For all your replys!

    I still cant manage to get it working. I have the below Code in the Default Program. Cs file.

    I think i have an issue with an entry point of the application.

    I have added the following line in to produce an error if the Job can't be started. And i know the job cant be started as the specified SQL server does not exist.

    What am i doing wrong?

     MessageBox.Show(ex.ToString(), "Failed to start the job :", MessageBoxButtons.OK, MessageBoxIcon.Error);

    This is the Program.cs contents

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Smo.Agent;
    using System.Timers;
    using Timer = System.Timers.Timer;
    
    
    
    namespace Capitalise
    {
        static class Program
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new MainMenu());
            }
        }
    
      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 Sqljob(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);
                    MessageBox.Show(ex.ToString(), "Failed to start the job :", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    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;
                }
            }
        }
    }
    

    • Marked as answer by Mike Feng Tuesday, January 8, 2013 10:08 AM
    Wednesday, January 2, 2013 10:03 PM

All replies

  • Hi Monk,

    Welcome to the MSDN Forum.

    Here is a dedicated class for Sql Job: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.aspx 

    This method can start a job: http://msdn.microsoft.com/en-us/library/ms202348.aspx 

    And this property represents the status: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.currentrunstatus.aspx

    Would you like to try it?

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 2, 2013 2:40 AM
  • Hi Mike,

    Many Thanks for your reply.

    Your suggestion is Great However i am looking for an example of simply

     - Connecting to the server

     - Starting The Job

     - Display a Message box of when the job has finished running.

    Wednesday, January 2, 2013 3:20 AM
  • Hi Monkman,

    Welcome to the MSDN Forum.

    OK, I got it.

    I check your code for read SP result, it should be OK.

    So what kind of "doesn't work " on your side?

    Would you like to tell us more about this? You got any exceptions? If so, what is the exception message?

    Or you just get empty result?

    Thank you very much.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 2, 2013 5:47 AM
  • Hi Dj_Monkman,

    Please have a look at this:

    http://msdn.microsoft.com/en-us/library/ms202348%28v=sql.105%29.aspx

    please check, if you can get your desired result by using event OnSuccessAction and here is one more reference on it:

    http://www.codeproject.com/Tips/367470/Manage-SQL-Server-Agent-Jobs-using-Csharp


    Regards, http://www.shwetalodha.blogspot.in/


    Wednesday, January 2, 2013 5:59 AM
  • Thanks For all your replys!

    I still cant manage to get it working. I have the below Code in the Default Program. Cs file.

    I think i have an issue with an entry point of the application.

    I have added the following line in to produce an error if the Job can't be started. And i know the job cant be started as the specified SQL server does not exist.

    What am i doing wrong?

     MessageBox.Show(ex.ToString(), "Failed to start the job :", MessageBoxButtons.OK, MessageBoxIcon.Error);

    This is the Program.cs contents

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Smo.Agent;
    using System.Timers;
    using Timer = System.Timers.Timer;
    
    
    
    namespace Capitalise
    {
        static class Program
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new MainMenu());
            }
        }
    
      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 Sqljob(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);
                    MessageBox.Show(ex.ToString(), "Failed to start the job :", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    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;
                }
            }
        }
    }
    

    • Marked as answer by Mike Feng Tuesday, January 8, 2013 10:08 AM
    Wednesday, January 2, 2013 10:03 PM
  • OK,  So i have it compiling now.

    I just need to call this function when the application starts.

    Wednesday, January 2, 2013 11:33 PM
  • OK,  So i have it compiling now.

    I just need to call this function when the application starts.

    Hi Monkman,

    Does this mean your above code work well?

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 3, 2013 1:22 AM