Start, Stop, or Pause the SQL Server Service

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Intelligent Solutions Inc.

Version Compatibility:

More information:

This example uses the Microsoft SQLDMO library to start, stop, or pause the SQL service for a specified SQL Server.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
Option Explicit Public Enum ServiceState et_Stop et_Pause et_Start End Enum

Code:

Public Function ToggleSQLService(ServerName As String, _
    State As ServiceState, UserID As String, _
    Password As String) As Boolean

'*************************************
'PURPOSE:       Start, Stop, or Pause SQL Server
'PARAMETERS:    ServerName: Name of SQLServer
'               State: Member of ServiceState Enum 
'                      (see declarations)
'               UserID: LoginID for SQL Server
'               Password: Password for SQL Server
'
'RETURNS:       True if successful, false otherwise
'REQUIRES:      Reference to Microsoft SQLDMO object library
'EXAMPLE:       ToggleSQLService "MyServer", et_Stop, -
'                   "sa", "myPassword"
'******************************************
Dim oSQLServer As New SQLDMO.SQLServer

If State < et_Stop Or State > et_Start Then Exit Function
On Error GoTo ErrorHandler

With oSQLServer
    .Start False, ServerName, UserID, Password
Select Case State
    Case et_Stop
        .Stop
    Case et_Pause
        .Pause
    Case et_Start
        .Continue
    End Select
End With
ToggleSQLService = True

ErrorHandler:
Set oSQLServer = Nothing
End Function