• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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
0
plq
Asked:
plq
  • 5
  • 3
  • 2
  • +1
1 Solution
 
mironCommented:
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
 
mironCommented:
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
 
ispalenyCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mironCommented:
you forgot that you just stopped sql server
:)

exec master.dbo.xp_cmdshell 'echo net start mssqlserver>>c:\restartsql.bat'
0
 
ispalenyCommented:
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
 
mironCommented:
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
 
crescendoCommented:
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
 
ispalenyCommented:
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
 
plqAuthor Commented:
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
 
mironCommented:
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
 
plqAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now