Solved

Restart Sql Server from a VB Program

Posted on 2004-08-06
11
361 Views
Last Modified: 2008-02-01
How can I restart sql server from a VB program ?

I have an ADODB connection or I don't mind if its a shell command but there must be no interaction

Thanks
0
Comment
Question by:plq
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:miron
ID: 11737337
the shell commands are

for nt 4.0
        net stop mssqlserver
        net start mssqlserver
works only locally

from windows xp
        sc <machine_name> start mssqlserver
        sc <machine_name> stop mssqlserver

the user need admin privileges on the machine where the sql server is re - started

-- cheers
0
 
LVL 9

Expert Comment

by:miron
ID: 11737373
well... on reverse :)

        sc <machine_name> stop mssqlserver
        sc <machine_name> start mssqlserver

the service status can be found this way

        sc <machine_name> query mssqlserver

the row to look for with possible values accordingly

"STATE              : { 1  STOPPED | 2  START_PENDING | : 3  STOP_PENDING | 4  RUNNING }"

-- cheers
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 11737461
Using ADODB connection

exec master.dbo.xp_cmdshell 'echo net stop mssqlserver>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'echo net start mssqlserver>>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'c:\restartsql.bat'
0
 
LVL 9

Expert Comment

by:miron
ID: 11737521
you forgot that you just stopped sql server
:)

exec master.dbo.xp_cmdshell 'echo net start mssqlserver>>c:\restartsql.bat'
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 11737762
Miron,

Try first, post later. The MSSQLSERVER service starts a separate process CMD.EXE.

This also works (adds 50 seconds between service stop and start)
-----------------------------------------
exec master.dbo.xp_cmdshell 'echo WScript.Sleep(50000)>c:\sleep.vbs'
exec master.dbo.xp_cmdshell 'echo net stop mssqlserver>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'echo Cscript c:\sleep.vbs //B >>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'echo net start mssqlserver>>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'c:\restartsql.bat'

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 9

Expert Comment

by:miron
ID: 11738102
exec master.dbo.xp_cmdshell 'echo WScript.Sleep(50000)>c:\sleep.vbs'
exec master.dbo.xp_cmdshell 'echo net stop mssqlserver>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'echo Cscript c:\sleep.vbs //B >>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'echo net start mssqlserver>>c:\restartsql.bat'
exec master.dbo.xp_cmdshell 'c:\restartsql.bat'

implies that your sql server is running as local machine adminsitrator, so I am not able even to try it :)

It would work, corrected I stand, this is highly *not recommended*

-- cheers
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11738211
From VB6 you need to run the following commands

        net stop mssqlserver
        net start mssqlserver
        net start SQLSERVERAGENT

You have to make sure that each command has completed before you run the next, so the standard VB "Shell" command isn't much use. I've listed a set of routines below that allow you to start a program and wait for it to complete. Check for a return code of zero to denote the command was successful.

All the best

crescendo


Option Explicit

Private Type STARTUPINFO
   cb As Long
   lpReserved As String
   lpDesktop As String
   lpTitle As String
   dwX As Long
   dwY As Long
   dwXSize As Long
   dwYSize As Long
   dwXCountChars As Long
   dwYCountChars As Long
   dwFillAttribute As Long
   dwFlags As Long
   wShowWindow As Integer
   cbReserved2 As Integer
   lpReserved2 As Long
   hStdInput As Long
   hStdOutput As Long
   hStdError As Long
End Type

Private Type PROCESS_INFORMATION
   hProcess As Long
   hThread As Long
   dwProcessID As Long
   dwThreadID As Long
End Type

Private Const SW_HIDE = &H0
Private Const STARTF_USESHOWWINDOW = &H1

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
   lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
   lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
   ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
   ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
   lpStartupInfo As STARTUPINFO, lpProcessInformation As _
   PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Public Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

' execute an external program and wait for it to terminate.

' this is included for compatibility with previous programs
' but the ExecCmdSync and ExecCmdAsync functions below are
' better because they give an exit code.

Public Sub ExecCmd(cmdline$, blnShow As Boolean)
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim Ret As Long
   
    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)
    If Not blnShow Then     ' hide window
        start.dwFlags = STARTF_USESHOWWINDOW
        start.wShowWindow = SW_HIDE
    End If
    ' Start the shelled application:
    Ret = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
   
    ' Wait for the shelled application to finish:
    Ret = WaitForSingleObject(proc.hProcess, INFINITE)
    Ret = CloseHandle(proc.hProcess)
End Sub

' execute an external program synchronously and wait for it to terminate,
' returning the exit code

Public Function ExecCmdSync(cmdline$, blnShow As Boolean) As Long
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim Ret As Long
   
    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)
    If Not blnShow Then     ' hide window
        start.dwFlags = STARTF_USESHOWWINDOW
        start.wShowWindow = SW_HIDE
    End If
    ' Start the shelled application:
    Ret = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
   
    ' Wait for the shelled application to finish:
    Ret = WaitForSingleObject(proc.hProcess, INFINITE)
   
    ' get the exit code  and close the handle
   
    Ret = GetExitCode(proc.hProcess)
    ExecCmdSync = Ret
   
End Function

' execute an external program asynchronously, returning a handle to the
' process so that its progress and exit status can be monitored

Public Function ExecCmdAsync(cmdline$, blnShow As Boolean) As Long
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim Ret As Long
   
    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)
    If Not blnShow Then     ' hide window
        start.dwFlags = STARTF_USESHOWWINDOW
        start.wShowWindow = SW_HIDE
    End If
    ' Start the shelled application:
    Ret = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
   
    ' return the process handle
   
    ExecCmdAsync = proc.hProcess
End Function

' check if an asynchronously shelled process is still executing

Public Function StillExecuting(hProcess As Long) As Boolean
    Const STILL_ACTIVE = 259&
    Dim lRet As Long
    Dim lCode As Long
   
    lRet = GetExitCodeProcess(hProcess, lCode)
    If lRet = 0 Then
        Debug.Print FormatLastError
    Else
        StillExecuting = (lCode = STILL_ACTIVE)
    End If
   
End Function

' get the exit code from a shelled process and close the process handle

Public Function GetExitCode(hProcess As Long) As Long
    Dim lRet As Long
    Dim lCode As Long
   
    lRet = GetExitCodeProcess(hProcess, lCode)
    If lRet = 0 Then
        Debug.Print FormatLastError
    Else
        GetExitCode = lCode
        lRet = CloseHandle(hProcess)
    End If
End Function

0
 
LVL 13

Expert Comment

by:ispaleny
ID: 11738269
miron,
are you able to run this?

exec master.dbo.xp_cmdshell 'echo net stop mssqlserver>%TEMP%\restartsql.bat'
exec master.dbo.xp_cmdshell 'echo net start mssqlserver>>%TEMP%\restartsql.bat'
exec master.dbo.xp_cmdshell '%TEMP%\restartsql.bat'
0
 
LVL 8

Author Comment

by:plq
ID: 11739181
Thanks chaps I think net stop and net start will do the job as its just a build program so only run by developers.

Having said that crescendo's solution would be most appropriate because the build program is written in VB

Got to go drinking beer now so I'll give it a go on Monday

Cheers
Paul
0
 
LVL 9

Accepted Solution

by:
miron earned 500 total points
ID: 11739623
while I admire the vb code  posted, and it definitely places us on the right track, here is the code from msdn

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting01142003.asp

this is basically the code from above, only wrapped and exposing much better API. Much better in the sence that

a) it handles internally wait functions and returns the set of status values that is easy to handle from VB, the "net stop" is not very friendly with VB.


strComputer = "."
strNamespace = "\root\cimv2"
strClass = "Win32_Service"
strKey = "Name"
strKeyValue = "MSSQLServer"

Set objSWbemServices = GetObject("winmgmts:\\" & strComputer & strNamespace)
Set colSWbemObjectSet = objSWbemServices.ExecQuery _
    ("SELECT * FROM " & strClass & " WHERE " & strKey & "='" & strKeyValue & "'")

For Each objSWbemObject in colSWbemObjectSet
     objSWbemObject.StopService()
     objSWbemObject.StartService()
Next

Here is the link to the WIN32_Service class description:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/startservice_method_in_class_win32_service.asp

Note, that cs.exe is a wrapper that sits on top of the same code WMI exposes via com components.

-- Cheers
0
 
LVL 8

Author Comment

by:plq
ID: 11750304
The above was the best solution, given that the base language is VB

This is a multiple instance server so I had to change the service name as below. That restarts the sql instance I'm working with OK.

sComputer = "."
sNamespace = "\root\cimv2"
sClass = "Win32_Service"
sKey = "Name"
sKeyValue = "MSSQL$INSTANCENAME"

Set objSWbemServices = GetObject("winmgmts:\\" & sComputer & sNamespace)
Set colSWbemObjectSet = objSWbemServices.ExecQuery _
    ("SELECT * FROM " & sClass & " WHERE " & sKey & "='" & sKeyValue & "'")

For Each objSWbemObject in colSWbemObjectSet
     objSWbemObject.StopService()
     objSWbemObject.StartService()
Next

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now