asked on

Restart Sql Server from a VB Program

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

miron

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

miron

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

ispaleny

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'

miron

you forgot that you just stopped sql server
:)

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

ispaleny

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'

miron

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

crescendo

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

ispaleny

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'

plq

ASKER

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

ASKER CERTIFIED SOLUTION

miron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plq

ASKER

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