How to check if SQL Server is running via asp.net

  • Thread starter Thread starter wwwmike
  • Start date Start date
W

wwwmike

Problem: When my provider shuts down the SQL Server IIS receives a few
error messages and then switches the site to 'SERVICE UNAVAILABLE'.

Before IIS kills itself it is friendly enough to send me an error
message.

****ERROR MESSAGE 1******
System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections.


This error message happens when the server is offline and my ASP.NET 2
code tries to open a connection

.....
myConnection.Open()
.....

The last error message I receive is

*****ERROR MESSAGE 2*****
System.InvalidOperationException: Timeout expired. The timeout period
elapsed prior to obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and max pool size
was reached


I am not sure if the ERROR MESSAGE 2 really is about the connection
pool, since the sql server is already down. But this might trigger IIS
to set my service unavailable.



What can I do in my asp.net code to detectd if a SQL server is online?
 
There are two specfic exceptions are thrown when you try to connect to the
back end i.e this line of code:

conn.Open()

1. InvalidOperationException

2. SqlException

What can I do in my asp.net code to detectd if a SQL server is online?

to resolve it ....catch SqlException error

If the Number property contains the value 18487 or 18488, this indicates
that the specified password has expired or must be reset. Check for Number
property to identify the cause of the issue..

Raj
 
re:
System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections.
Click to expand...

Networking protocols are disabled by default in SQL Server Express.

If someone simply installs Express and chooses all the defaults, SQL Server Express will only
be able to have connections originating on the local machine where SQL Server is installed.

You need to expressly enable networking protocols.

To fix that, follow the instructions outlined at :
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
or
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=271795&SiteID=1



Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================
 
Wrap your first SQL call in a try /catch /finally block and output a friendly
message from within the exception block.
Peter
 
Back
Top