Hi BIll,
Here's what I do. The key is an sproc I've created called testdatabase.
Here is its code:
CREATE PROCEDURE testdatabase AS
select databasepropertyex('imc','status')
where 'imc' is the name of the server to check. The code returns a string,
which I then report in the statusbar of my app, as follows:
Dim oconn As New SqlConnection(globalconnectionstring)
oconn.Open()
Dim ocmd As New SqlCommand
ocmd = New SqlCommand("exec testdatabase", oconn)
Dim statusstring As String
Try
statusstring = ocmd.ExecuteScalar()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oconn.Close()
If statusstring = "ONLINE" Then
StatusBar1.Panels(6).Text = "Database Online"
Me.ErrorProvider1.SetError(StatusBar1, "")
ElseIf statusstring = "OFFLINE" Then
StatusBar1.Panels(6).Text = "Database Offline"
Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)
Me.ErrorProvider1.SetError(StatusBar1, "Database Offline")
ElseIf statusstring = "RESTORING" Then
StatusBar1.Panels(6).Text = "Database Restoring"
Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)
Me.ErrorProvider1.SetError(StatusBar1, "Database Restoring")
ElseIf statusstring = "RECOVERING" Then
StatusBar1.Panels(6).Text = "Database Recovering"
Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)
Me.ErrorProvider1.SetError(StatusBar1, "Database Recovering")
ElseIf statusstring = "SUSPECT" Then
StatusBar1.Panels(6).Text = "Database Suspect"
Me.ErrorProvider1.SetIconAlignment(StatusBar1, ErrorIconAlignment.TopLeft)
Me.ErrorProvider1.SetError(StatusBar1, "Database Malfunctioning - Call
Cherwell Immediately")
End If
HTH,
Bernie Yaeger
"William Oliveri" <wu**@bigvalley.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Hi all,
I'm writing some code for disaster recovery. I need to have an
application which accesses Sql Server to determine if the local database is up or not
and then if not go to the secondary server.
So like this:
1. Determine if Sql Server A is up, if so is the database active.
2. If no on either count, go to Sql Server B/database.
Is there some way to query Sql Server through the Dot Net framework to
determine the server/database status?
Thank in advance,
Bill