We help IT Professionals succeed at work.

Shut down and Restart SQL Server 7

schworak
schworak asked
on
I am looking for a code sample that will shut down SQL server 7 and then restart it after my code is finished running.

I would prefer if the code was in the format of subroutines that I could just call, but any code sample that can do the job will do.
Comment
Watch Question

You need to use SQLDMO for this (the only way I know at least).  Example:

"Start, Stop, or Pause the SQL Server Service"
http://www.freevbcode.com/ShowCode.Asp?ID=1169
Commented:
Ok I have some funky code that I found and am using on a regular basis.

The first segment STARTS SQL

Public Sub sStartMSDE(ByVal sSvrName As String, ByVal sUID As String, ByVal sPWD As String)
'********************************************************************
'This procedure will turn on MSDE. If the server has already been started
'the error trap will exit the procedure and leave the server running.

'Note that it will not place the SQL Service Manager on the Task Bar.
'
'Input:
'   sSvrName    The server to be started
'   sUID        The user ID with which to start the server
'   sPWD        The user password
'
'Output:
'   Resolution of start
'
'References:
'   Microsoft SQLDMO Object Library
'********************************************************************

    Dim oSvr As SQLDMO.SQLServer
   
    'Create the SQLDMO Server Object.
    Set oSvr = CreateObject("SQLDMO.SQLServer")
       
    On Error GoTo StartError
           
    oSvr.LoginTimeout = 60
    'Start Server.
    logStream.Write Chr(13) & "Executing Start of SQLDMO Server in sStartMSDE()" & Chr(13)
    oSvr.Start True, sSvrName, sUID, sPWD
   
   
ExitSub:
    Exit Sub

StartError:
    If Err.Number = -2147023840 Then
       'This error occurs when the server is already running,
       'and Server.Start is executed on NT.
         
       oSvr.Connect sSvrName, sUID, sPWD 'Connect to server.
 
    Else 'Unknown error
        MsgBox Err.Description
    End If
   
    logStream.WriteLine Chr(13) & "Known Error occurred when starting MSDE in sStartMSDE()"
    logStream.WriteLine "Error Number: " & Err.Number
    logStream.WriteLine "Error text: " & Err.Description
    Resume ExitSub
   
End Sub


Next the code that stops SQL server

Public Sub sStopMSDE(sSvrName As String, sUID As String, sPWD As String)
'********************************************************************
'This procedure will stop MSDE. If the server has already been stopped
'the error trap will exit the procedure and leave the server stopped.

'Note that it will not place the SQL Service Manager on the Task Bar.
'
'Input:
'   sSvrName    The server to be started
'   sUID        The user ID with which to start the server
'   sPWD        The user password
'
'Output:
'   Resolution of stop
'
'References:
'   Microsoft SQLDMO Object Library
'********************************************************************

    Dim oSvr As SQLDMO.SQLServer

    'Create the SQLDMO Server Object.
    Set oSvr = CreateObject("SQLDMO.SQLServer")
           
    On Error GoTo StopError
   
    oSvr.LoginTimeout = 60
    oSvr.Connect sSvrName, sUID, sPWD 'Connect to server.
    oSvr.Stop
   
ExitSub:
    Exit Sub

StopError:
   
    Resume ExitSub
   
End Sub

Commented:
Oh,  Sorry I forgot to remove the Logstream portions of the StartMSDE function call.  These are simply lines I have been using to debug a beta version of some software.   They can be safely deleted.

Commented:
you can use net command :

net stop SQLServerAgent  
net stop MSSQLServer

net start MSSQLServer
net start SQLServerAgent  


Explore More ContentExplore courses, solutions, and other research materials related to this topic.