I'm looking for a way to check SQL Server availability like this MySQL solution, unless there is a better method.
My plan is to check if the SQL Server instance is up/down, and if it is down then display a message to the user, all via a try/catch.
-
is the sql server local or remote (or both)?djangofan– djangofan2009-07-30 14:14:30 +00:00Commented Jul 30, 2009 at 14:14
-
remote, i.e. it is on a different server but inside of the same domainBryan Denny– Bryan Denny2009-07-30 14:27:05 +00:00Commented Jul 30, 2009 at 14:27
Add a comment
|
3 Answers
I would add an exception to the top of your try/catch/finally to look for the sql connection status. I forget the object and property, but I'm sure you can find that out. The typical SQL way of querying (that I'm used to):
using (SqlConnection) {
try {
// open connection, try to execute query and fetch results
} catch (Connection.IsOpen) {
// add the catch exception for connection status here
} finally {
//close connection
}
}
Comments
Here's a programmatic solution using SMO (Server Management Objects)...
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server();
try
{
// You can modify your connection string here if necessary
server.ConnectionContext.ConnectionString = "Server=servername; User ID=username; Password=password";
server.ConnectionContext.Connect();
}
catch (Exception ex)
{
// Handle your exception here
}
if (server.ConnectionContext.IsOpen)
{
// Show message that the server is up
}
else
{
// Show message that the server is down
}
2 Comments
marc_s
What's the benefit of doing this over just simply trying to open a SqlConnection and if it fails, react on it?
Donut
Well honestly in this example, there's not much difference that I can see. In general I tend to prefer using SMO because it's so powerful, enabling "programming all aspects of managing Microsoft SQL Server" - you can programmatically do pretty much whatever you want. But it seems like for this simple operation, either this or SqlConnection would just as well. msdn.microsoft.com/en-us/library/ms162169.aspx
I would create a new connection string with a very small connect timeout
Dim Conn As New SqlClient.SqlConnection("server=127.0.0.1;uid=username;pwd=password;database=mydatabasename;Connect Timeout=5")
Try
Conn.Open()
Catch exSQL As SqlClient.SqlException
If exSQL.Message.ToUpper().Contains("LOGIN FAILED") Then
MsgBox("Invalid User/Password")
Else
MsgBox("SQL Error: " & exSQL.Message)
End If
Exit Sub
Catch ex As Exception
MsgBox("Something went wrong.")
Exit Sub
Finally
Conn.Close()
Conn.Dispose()
End Try
