restarting remote SQL server

  • Thread starter Thread starter ECathell
  • Start date Start date
E

ECathell

Below is my code the stop works fine, but then my session is ended. And the rest of the code wont run. I was thinking of making it two different subs, but from the examples in the samples folder I shouln't have to.





Dim srv1 As SQLServer

srv1 = New SQLDMO.SQLServer

If Not srv1 Is Nothing Then

srv1.DisConnect()

srv1 = Nothing

srv1 = New SQLDMO.SQLServer

End If



With srv1

.LoginTimeout = 10

.Name = servername

.Login =

.Password =

.Connect()

End With



'Invoke command to stop server and wait

'until it stops

Select Case srv1.JobServer.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.JobServer.Stop()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

srv1.JobServer.Stop()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

MessageBox.Show("SQlAgent is already Stopped")

End Select

Select Case srv1.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.Shutdown()

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

srv1.Shutdown()

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

MessageBox.Show("SQlServer is already Stopped")



End Select



'Start jobs back up.

Select Case srv1.JobServer.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.JobServer.Start()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

MessageBox.Show("SQlAgent is already Running")



Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

srv1.JobServer.Start()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

End Select

Select Case srv1.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.Start(False, srv1.Name)

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

MessageBox.Show("SQlAgent is already Running")



Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

srv1.Start(False, srv1.Name)

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

End Select
 
never mind I figured it out on my own.


--
--Eric Cathell, MCSA
Below is my code the stop works fine, but then my session is ended. And the rest of the code wont run. I was thinking of making it two different subs, but from the examples in the samples folder I shouln't have to.





Dim srv1 As SQLServer

srv1 = New SQLDMO.SQLServer

If Not srv1 Is Nothing Then

srv1.DisConnect()

srv1 = Nothing

srv1 = New SQLDMO.SQLServer

End If



With srv1

.LoginTimeout = 10

.Name = servername

.Login =

.Password =

.Connect()

End With



'Invoke command to stop server and wait

'until it stops

Select Case srv1.JobServer.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.JobServer.Stop()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

srv1.JobServer.Stop()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

MessageBox.Show("SQlAgent is already Stopped")

End Select

Select Case srv1.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.Shutdown()

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

srv1.Shutdown()

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

MessageBox.Show("SQlServer is already Stopped")



End Select



'Start jobs back up.

Select Case srv1.JobServer.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.JobServer.Start()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

MessageBox.Show("SQlAgent is already Running")



Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

srv1.JobServer.Start()

Do Until srv1.JobServer.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

End Select

Select Case srv1.Status

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused

srv1.Start(False, srv1.Name)

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

MessageBox.Show("SQlAgent is already Running")



Case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped

srv1.Start(False, srv1.Name)

Do Until srv1.Status = SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running

Loop

End Select
 
Back
Top