June 6, 2002 at 10:32 pm
I can start or stop Sql server using DMO. But how can I detect that current state of Sql? In other words, how can I tell if Sql Server is already up and running (or stopped) on a computer?
Bill
June 7, 2002 at 5:52 am
Look at the SQLServer Object item in BOL. The property you will use is Status which will return the state of the service.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 8, 2002 at 5:08 pm
I'm trying to determine if Sql server is running on a given machine. I turned off the Sql Server on "tower2" using the SQL Server Service Manager, emulating a failure.
In the enlosed code, since Sql Server is not running, the line "oSQLServer.Connect "tower2"" generates an error. Because of "On Error Resume Next", code execution falls into
(if(oSQLServer.Status = SQLDMOSvc_Running))
Now tooltips shows that SQLDMOSvc_Running = 1 and oSQLServer.Status = <SQL-DMO[Service Controller Error] The RPC Server is unavaliable>. The funny part is that VB regards them as being equal and displays my message box "sql running".
What am I doing wrong here?
TIA,
Bill
Private Sub Command1_Click()
On Error Resume Next
Dim oSQLServer As SQLDMO.SQLServer
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect "tower2"
If (oSQLServer.Status = SQLDMOSvc_Running) Then
MsgBox "sql running!"
Else
MsgBox "sql NOT running!"
End If
Set oSQLServer = Nothing ' Done
End Sub
June 8, 2002 at 5:52 pm
I paused my service, then ran it, got the error:
-2147023174 [SQL-DMO]Service Control Error: The RPC server is unavailable.
Trying again with service stopped:
Error : -2147221504-[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()).
Doesnt look like status is much help, you have to trap the error anyway, so I'd say set a flag in the error handler and then branch in your code appropriately. Only other comment is that it's recommended to use "osqlserver.disconnect" prior to setting the object to nothing.
Andy
July 1, 2002 at 4:57 am
Hi guys
i had the same problem and the solution is this!
Set m_Server = CreateObject("SQLDMO.SQLServer")
m_Server.Name = "SERVERNAME"
if m_Server.Status <> SQLDMOSvc_Running then
msgbox "bla..bla...bla"
end if
bye
Antonio
July 6, 2002 at 7:18 pm
Hey Antonio,
Great post! It NEVER occurred to me that the name property would be read/write. That's a lot cleaner than trapping the error.
Andy
July 8, 2002 at 1:02 am
Thanks Andy
I'm glad to help you!
Antonio
July 8, 2002 at 11:07 am
Another way to tell is use the sc command (at a dos prompt), found in the windows resource kit. It can be used to manage all the services on a specified machine. If you need to check the results programatically, run it in xp_cmdshell and return the results into a temp table. I manage a number of sql servers connected on a slow network and have found this to be very helpful. An example use would be:
sc \\<<machine name>> start|pause|stop|query|other_options <<service name, like mssqlserver>>
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply