xp_servicecontrol - sp_get_SQL_Server_Agent_status - SQL Server Agent management - How to develop Mobile Management Tool For SQL Server - Siccolo - SQL Management Tool

(IV) - 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 VII - Developing SQL Server Agent related control management interfaces (sp_get_SQL_Server_Agent_status, sp_start_SQL_Server_Agent,

First, let's show the user current SQL Server Agent status:

	Public Function GetSQLServerAgentStatus(ByVal oLogInf As LoginInfo, _
                                        ByVal ToDebug As Boolean, _
                                        ByRef SQLServerAgentStatus As SQLServerAgentStatuses, _
                                        ByRef ErrorInfo As String) As Boolean
            Try
                oLoginInfo = oLogInf

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

                Dim objCommand As SqlCommand = New SqlCommand("sp_get_SQL_Server_Agent_status", objConnection)
                objCommand.CommandType = CommandType.StoredProcedure

                Dim objData As SqlClient.SqlDataReader
                objData = objCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow Or _
                                                    System.Data.CommandBehavior.CloseConnection)

                With objData
                    Dim strStatus = ""
                    .Read()
                    strStatus = Trim(.GetString(.GetOrdinal("status")))
                    Select Case True
                        Case InStr(strStatus, "Running", CompareMethod.Text) > 0
                            SQLServerAgentStatus = SQLServerAgentStatuses.Running

                        Case InStr(strStatus, "Stopped", CompareMethod.Text) > 0
                            SQLServerAgentStatus = SQLServerAgentStatuses.Stopped

                        Case InStr(strStatus, "Stopping", CompareMethod.Text) > 0
                            SQLServerAgentStatus = SQLServerAgentStatuses.Stopping

                        Case InStr(strStatus, "Starting", CompareMethod.Text) > 0
                            SQLServerAgentStatus = SQLServerAgentStatuses.Starting

                        Case Else
                            SQLServerAgentStatus = SQLServerAgentStatuses.Unknown
                    End Select
                End With

                DisConnect()

                Return True

            Catch ex_get_status As Exception
                SQLServerAgentStatus = SQLServerAgentStatuses.ErrorRetrieving
                ErrorInfo = ex_get_status.Message  'ex_execute.ToString
                Return False
            End Try
        End Function

* where type SQLServerAgentStatuses:

	 Public Enum SQLServerAgentStatuses
            Stopped = 0
            Running = 1
            Stopping = 2
            Starting = 3
            Unknown = 99
            ErrorRetrieving = -99
        End Enum

And, then client will:

	Friend Function GetSQLServerAgentStatus(ByRef SQLServerAgentStatus As SQLServerAgentStatuses, _
                                            ByRef ErrorInfo As String) As Boolean

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

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

            Return True

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

Next, let's see how we can control SQL Server Agent (to stop SQL Server Agent simply call sp_stop_SQL_Server_Agent system stored procedure):

	 Public Function StartSQLServerAgent(ByVal oLogInf As LoginInfo, _
                                            ByVal ToDebug As Boolean, _
                                            ByRef NewSQLServerAgentStatus As SQLServerAgentStatuses, _
                                            ByRef ErrorInfo As String) As Boolean
            Try
                oLoginInfo = oLogInf

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

                Dim objCommand As SqlCommand = New SqlCommand("sp_start_SQL_Server_Agent", objConnection)
                objCommand.CommandType = CommandType.StoredProcedure

                Dim objData As SqlClient.SqlDataReader
                objData = objCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow Or _
                                                    System.Data.CommandBehavior.CloseConnection)

                With objData
                    Dim strStatus = ""
                    .Read()
                    strStatus = Trim(.GetString(.GetOrdinal("status")))
                    Select Case True
                        Case InStr(strStatus, "Running", CompareMethod.Text) > 0
                            NewSQLServerAgentStatus = SQLServerAgentStatuses.Running

                        Case InStr(strStatus, "Stopped", CompareMethod.Text) > 0
                            NewSQLServerAgentStatus = SQLServerAgentStatuses.Stopped

                        Case InStr(strStatus, "Stopping", CompareMethod.Text) > 0
                            NewSQLServerAgentStatus = SQLServerAgentStatuses.Stopping

                        Case InStr(strStatus, "Starting", CompareMethod.Text) > 0
                            NewSQLServerAgentStatus = SQLServerAgentStatuses.Starting

                        Case Else
                            NewSQLServerAgentStatus = SQLServerAgentStatuses.Unknown
                    End Select
                End With

                DisConnect()

                Return True

            Catch ex_start_agent As Exception
                NewSQLServerAgentStatus = SQLServerAgentStatuses.ErrorRetrieving
                ErrorInfo = ex_start_agent.Message  '
                Return False
            End Try
        End Function

where stored procedures are:

	create procedure sp_get_SQL_Server_Agent_status
	as
	set nocount on

	create table #sql_agent_status
		( status varchar(50) )

	insert into #sql_agent_status(status)
	exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'

	select status from #sql_agent_status

	drop table #sql_agent_status
	set nocount off

	create procedure sp_start_SQL_Server_Agent
	as
	set nocount on

	create table #sql_agent_status
		( status varchar(50) )

	insert into #sql_agent_status(status)
	exec master.dbo.xp_servicecontrol 'START', 'SQLServerAgent'

	insert into #sql_agent_status(status)
	exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'

	select status from #sql_agent_status

	drop table #sql_agent_status
	set nocount off

	create procedure sp_stop_SQL_Server_Agent
	as
	set nocount on

	create table #sql_agent_status
		( status varchar(50) )

	insert into #sql_agent_status(status)
	exec master.dbo.xp_servicecontrol 'STOP', 'SQLServerAgent'

	insert into #sql_agent_status(status)
	exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'

	select status from #sql_agent_status

	drop table #sql_agent_status
	set nocount off

Side Note:

CompareMethod - When you call comparison functions, you can use the CompareMethod enumeration in your code in place of the actual values. CompareMethod.Binary is for a binary comparison, and CompareMethod.Text performs a textual comparison (i.e. non-casesensitive)

Back to (III)

(I)

Continue to (V)