I'm using this function to return a value from multiple databases across three SQL instances (On the same server). I'm looping though a DataGridView that lists all the databases on one of the three SQL instances.
Is there a faster way of doing this? It's quite slow using this method.
Function DatabaseStatus(ByVal SQLServer As String, ByVal Database As String)
Dim myConn As New SqlConnection("Server=" & SQLServer & ";User Id=USER;Password=PASSWORD;Database=" & Database & ";")
Dim Status As String = ""
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
Dim query As String = "SELECT STATEMENT;"
Dim myCommand As New SqlCommand(query, myConn)
Try
If myCommand.ExecuteScalar().ToString.ToUpper = "OK" Then
Status = "Ready"
End If
Catch ex As Exception
Status = "Unknown"
Finally
myConn.Dispose()
End Try
Return Status
End Function
EDIT - SELECT statement example:
IF OBJECT_ID('TABLENAME') IS NOT NULL SELECT [Setting] FROM [TABLENAME]
WHERE [Section] = 'platform' and [Setting] = 'server' ELSE SELECT 'UNKNOWN';