Solved

Restart Sql Server from a VB Program

Posted on 2004-08-06
11
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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