Determining Status of Sql Server via SQL-DMO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Determining Status of Sql Server via SQL-DMO

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-11 : 10:15:54

bill writes "OS: Win NT, SP4
SQL: Sql7 or Sql 2k

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

I find thie question hard. I hope you don't!

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"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-11 : 10:22:00

pffft tooltips

here is a bit of code I use



Function ConnectToServer(sName, sUser, sPass)
    
    Dim RetVal
    Dim oSQL
    Set oSQL = CreateObject("SQLDMO.SQLServer")
    
    On Error Resume Next
    
    Call oSQL.Connect(sName, sUser, sPass)
    If Err.Number = 0 Then
        RetVal = 0
       
    Else
        RetVal = 1
    End If
    oSQL.DisConnect
    Set oSQL = Nothing
        
    ConnectToServer = RetVal


End Function

Damian

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-11 : 16:08:57

You don't need to connect to a server to see its status i.e.

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.Name ="tower2"

If (oSQLServer.Status = 1) Then
MsgBox oSQLServer.Status & "|sql running!"
Else
MsgBox oSQLServer.Status & "|sql NOT running!"
End If

Set oSQLServer = Nothing

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources