Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
Main     
(III) - How To Develop SQL Mobile Management Tool (and How I created one) - Continued:
Engineered to help you extend your support from mobile devices for Microsoft SQL Servers.

Part V - Developing SQL Server Jobs related management interfaces
(using sp_get_msdb_job_info, sp_update_job, sp_start_job)
 
Ok, so far so good. I was able to get databases, tables, views, stored procedures.
    In terms of SQL Server Agent management, I needed just a few things:
  1. Be able to view SQL Server jobs (as well as execution history)
  2. Be able to start SQL Server jobs
  3. Be able to start/stop SQL Server Agent
  4. Be able to restart SQL Server service
Next step - need to get SQL Server jobs and job related information.
Let's see how we can retrieve job list out of SQL Server: 
	 Public Function GetJobList(ByVal oLogInf As LoginInfo, _
                                    ByVal ToDebug As Boolean, _
                                    ByRef JobList_XML As String, _
                                    ByRef ErrorInfo As String) As Boolean

            oLoginInfo = oLogInf

            Dim objCommand As SqlCommand = New SqlCommand()
            objCommand = Me.ReturnCommand("sp_get_msdb_job_info", ToDebug, ErrorInfo)
            objCommand.CommandType = CommandType.StoredProcedure

            Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter()
            objDataAdapter.SelectCommand = objCommand

            Dim objFullJobListDataSet As DataSet = New DataSet("QueryResults")
            objDataAdapter.Fill(objFullJobListDataSet, "Jobs")

            Dim objStringWriter As New System.IO.StringWriter()
            objFullJobListDataSet.WriteXml(objStringWriter, XmlWriteMode.WriteSchema)   ', XmlWriteMode.IgnoreSchema)

            JobList_XML = "" & objStringWriter.ToString()

            Return True
        End Function

And, mobile client displays job list: 
	Friend Function PopulateJobList(ByVal jobsNode As Windows.Forms.TreeNode, _
                                   ByRef ErrorInfo As String) As Boolean

        Try
            ErrorInfo = ""
            Dim JobList_XML As String
            JobList_XML = objSQLWebServiceManager.JobList(objLoginInfo.ServerAddress, _
                                        objLoginInfo.UserName, _
                                        objLoginInfo.UserPassword, _
                                        ErrorInfo)
            If ErrorInfo <> "" Or JobList_XML = "" Then Return False

            Dim objResponse As New DataSet()
            Dim Reader As New System.IO.StringReader(JobList_XML.ToString())
            Dim XMLReader As New System.Xml.XmlTextReader(Reader)
            XMLReader.Read()

            objResponse.ReadXml(XMLReader, XmlReadMode.Auto)

            objResponse.AcceptChanges()

            XMLReader.Close()

            Dim newNode As Windows.Forms.TreeNode = New Windows.Forms.TreeNode()

            If (jobsNode.Nodes.Count > 0) Then 'Check we have already listed the DB's. If it so then remove it and rebuild it
                jobsNode.Nodes.Clear()
            End If

            Dim objDataRow As DataRow
            Dim strNode As String = ""
            For Each objDataRow In objResponse.Tables(0).Rows
                strNode = objDataRow("Job").ToString()
                newNode = jobsNode.Nodes.Add(strNode)
                newNode.Tag = "job-" & objDataRow("Job_id").ToString()
            Next

            Return True

        Catch ex As Exception
            ErrorInfo = ex.Message
            If m_LogErrorToFile Then LogError("PopulateJobList():" & ex.ToString)
            Return False
        End Try
    End Function

    Friend Function PopulateJobList(ByVal dataGridControl As Windows.Forms.DataGrid, _
                                   ByRef ErrorInfo As String) As Boolean

        Try
            ErrorInfo = ""
            Dim JobList_XML As String
            JobList_XML = objSQLWebServiceManager.JobList(objLoginInfo.ServerAddress, _
                                        objLoginInfo.UserName, _
                                        objLoginInfo.UserPassword, _
                                        ErrorInfo)
            If ErrorInfo <> "" Or JobList_XML = "" Then Return False

            Dim objResponse As New DataSet()
            Dim Reader As New System.IO.StringReader(JobList_XML.ToString())
            Dim XMLReader As New System.Xml.XmlTextReader(Reader)
            XMLReader.Read()

            objResponse.ReadXml(XMLReader, XmlReadMode.Auto)

            objResponse.AcceptChanges()

            XMLReader.Close()
            dataGridControl.DataSource = objResponse.Tables(0)

            Return True

        Catch ex As Exception
            ErrorInfo = ex.Message
            If m_LogErrorToFile Then LogError("PopulateJobList():" & ex.ToString)
            Return False
        End Try
    End Function

To retrieve job information with the execution history, as I wanted, I needed to execute two stored procedures: msdb.dbo.sp_help_job and msdb.dbo.sp_help_jobhistory like this: 
	Public Function GetJobInfoWithJobHistory(ByVal oLogInf As LoginInfo, _
                                                ByVal JobID As String, _
                                                ByVal ToDebug As Boolean, _
                                                ByRef JobInfo_XML As String, _
                                                ByRef ErrorInfo As String) As Boolean

            oLoginInfo = oLogInf

            Dim objDataSet As New DataSet()
            Dim RowCount As Integer
            Dim SqlCommand As String
            SqlCommand = "exec msdb.dbo.sp_help_job '" & JobID & "'" & _
                        ";exec msdb.dbo.sp_help_jobhistory @job_id='" & JobID & "', @mode='FULL' "

            If ReturnDataset(SqlCommand, ToDebug, objDataSet, RowCount, ErrorInfo) Then

                Dim objStringWriter As New System.IO.StringWriter()
                objDataSet.WriteXml(objStringWriter, XmlWriteMode.WriteSchema)

                JobInfo_XML = "" & objStringWriter.ToString()

                Return True
            Else

                Return False
            End If

        End Function
* where ReturnDataset() creates DataAdapter, DataSet, fills DataSet and returns RowCount

Somewhere, somehow I got cold - but(!) - went to Clarity Medispa and got a boost of B12! By the way, I highly recommend Colonic Therapy - get your colon cleaned and your mind will clear!

Anyway, back to....Then, to display job information on the user mobile device:
(see also article on SQL Server - How to Find SQL Server Job Execution Status) 
	Friend Function GetJobInfo(ByVal JobID As String, _
                                    ByRef JobEnabled As Boolean, _
                                    ByRef LastRunDate As String, _
                                    ByRef LastRunTime As String, _
                                    ByRef LastRunStatus As String, _
                                    ByRef CurrentStatus As String, _
                                    ByRef CurrentExecutionStep As String, _
                                    ByRef dataGridJobSteps As Windows.Forms.DataGrid, _
                                    ByRef dataGridJobSchedule As Windows.Forms.DataGrid, _
                                    ByRef dataGridJobHistory As Windows.Forms.DataGrid, _
                                   ByRef ErrorInfo As String) As Boolean

        Try
            ErrorInfo = ""

            Dim JobInfo_XML As String
          
            JobInfo_XML = objSQLWebServiceManager.JobInfoWithHistory(objLoginInfo.ServerAddress, _
                                                            objLoginInfo.UserName, _
                                                            objLoginInfo.UserPassword, _
                                                            JobID, _
                                                            ErrorInfo)

            If ErrorInfo <> "" Or JobInfo_XML = "" Then Return False

            Dim objResponse As New DataSet()
            Dim Reader As New System.IO.StringReader(JobInfo_XML.ToString())
            Dim XMLReader As New System.Xml.XmlTextReader(Reader)
            XMLReader.Read()

            objResponse.ReadXml(XMLReader, XmlReadMode.Auto)

            objResponse.AcceptChanges()

            XMLReader.Close()

            'first row - Job information
            Dim objDataRow As DataRow = objResponse.Tables(0).Rows(0)
            With objDataRow
                JobEnabled = .Item("enabled")
                LastRunDate = .Item("last_run_date")
                LastRunTime = .Item("last_run_time")
                LastRunStatus = .Item("last_run_outcome")
                CurrentStatus = .Item("current_execution_status")
                CurrentExecutionStep = .Item("current_execution_step")
            End With

            dataGridJobSteps.DataSource = objResponse.Tables(1)

            dataGridJobSchedule.DataSource = objResponse.Tables(2)

            dataGridJobHistory.DataSource = objResponse.Tables(4)

            Return True

        Catch ex As Exception
            ErrorInfo = ex.Message
            If m_LogErrorToFile Then LogError("GetJobInfo():" & ex.ToString)
            Return False
        End Try
    End Function
Part VI - Developing SQL Server Jobs control management interfaces (Enable/Disable/Start)
 
Not too hard either. Essentially, to enable or to disable a job is the same function. But I broke it into two separate ones just for readability: 
	Public Function DisableJob(ByVal oLogInf As LoginInfo, _
                                        ByVal JobID As String, _
                                        ByVal ToDebug As Boolean, _
                                        ByRef ErrorInfo As String) As Boolean

            oLoginInfo = oLogInf

            If (objConnection.State.ToString() <> "Open") Then
                Connect(ToDebug, ErrorInfo)
            End If

            'Return Code Values
            '0 (success) or 1 (failure)
            'objCommand = ReturnCommand("msdb.dbo.sp_update_job", ErrorInfo)
            Dim objCommand As SqlCommand = New SqlCommand("msdb.dbo.sp_update_job", objConnection)
            objCommand.CommandType = CommandType.StoredProcedure

            Dim objParmJobID As SqlParameter = objCommand.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier)
            objParmJobID.Value = New Guid(JobID)

            Dim objParmEnableDisable As SqlParameter = objCommand.Parameters.Add("@enabled ", SqlDbType.TinyInt)
            objParmEnableDisable.Value = 0  'disable job!

            objCommand.ExecuteNonQuery()

            DisConnect()

            Return True

        End Function

	Public Function EnableJob(ByVal oLogInf As LoginInfo, _
                                        ByVal JobID As String, _
                                        ByVal ToDebug As Boolean, _
                                        ByRef ErrorInfo As String) As Boolean

            oLoginInfo = oLogInf

            If (objConnection.State.ToString() <> "Open") Then
                Connect(ToDebug, ErrorInfo)
            End If

            'Return Code Values
            '0 (success) or 1 (failure)
            'objCommand = ReturnCommand("msdb.dbo.sp_update_job", ErrorInfo)
            Dim objCommand As SqlCommand = New SqlCommand("msdb.dbo.sp_update_job", objConnection)
            objCommand.CommandType = CommandType.StoredProcedure

            Dim objParmJobID As SqlParameter = objCommand.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier)
            objParmJobID.Value = New Guid(JobID)

            Dim objParmEnableDisable As SqlParameter = objCommand.Parameters.Add("@enabled ", SqlDbType.TinyInt)
             objParmEnableDisable.Value = 1  'enable job!

            objCommand.ExecuteNonQuery()

            DisConnect()

            Return True

        End Function
Notice using the same msdb.dbo.sp_update_job to enable or to disable a job. (sp_update_job - Changes the attributes of a job - such as job name, job description, start step, category)

On the client: (to disable a job, simply call DisableJob() method) 
	Friend Function EnableJob(ByVal JobID As String, ByRef ErrorInfo As String) As Boolean

        Try
            ErrorInfo = ""
            Dim Result As String = ""
            Result = objSQLWebServiceManager.EnableJob(objLoginInfo.ServerAddress, _
                                                    objLoginInfo.UserName, _
                                                    objLoginInfo.UserPassword, _
                                                    JobID, _
                                                    ErrorInfo)

            If ErrorInfo <> "" Or Result = "" Then Return False

            Return True

        Catch ex As Exception
            ErrorInfo = ex.Message
            If m_LogErrorToFile Then LogError("EnableJob():" & ex.ToString)
            Return False
        End Try
    End Function
To run a SQL Server Job: 
	Public Function RunJob(ByVal oLogInf As LoginInfo, _
                                        ByVal JobID As String, _
                                        ByVal ToDebug As Boolean, _
                                        ByRef ErrorInfo As String) As Boolean

            oLoginInfo = oLogInf

            If (objConnection.State.ToString() <> "Open") Then
                Connect(ToDebug, ErrorInfo)
            End If

            'Return Code Values
            '0 (success) or 1 (failure)
            Dim objCommand As SqlCommand = New SqlCommand("msdb.dbo.sp_start_job", objConnection)
            objCommand.CommandType = CommandType.StoredProcedure

            Dim objParmJobID As SqlParameter = objCommand.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier)
            objParmJobID.Value = New Guid(JobID)

            objCommand.ExecuteNonQuery()

            DisConnect()

            Return True

        End Function
  Back to (II)      (I)      Continue to (IV)
Article keywords: SQL Server Agent Get Job, SQL Server Agent, sp_get_msdb_job_info, Connection.State, SqlCommand, SqlDataAdapter, SelectCommand, DataSet, DataAdapter.Fill(), StringWriter, WriteXml(), StringReader, XMLReader.Read(), ReadXml(), Windows.Forms.TreeNode, DataSet.Tables(), dataGridControl.DataSource, msdb.dbo.sp_help_job, msdb.dbo.sp_help_jobhistory, last_run_date, last_run_time, last_run_outcome, current_execution_status, current_execution_step, sp_update_job, SqlCommand("msdb.dbo.sp_update_job"), Command.Parameters.Add("@enabled"), msdb.dbo.sp_update_job, Command.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier), sp_start_job

Back To Articles Page
Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky © 2006-2007
or share your thoughts at Siccolo Blog
Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.