WHY ADO _ConnectionPtr->State = adStateOpen | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

WHY ADO _ConnectionPtr->State = adStateOpen

even after database is shutdown, for example, thru the services.
I didn’t close the connection thru the program. If the db is down, dead, how can ADO _ConnectionPtr still connected
to database (which is nowhere to find)? What is a more reliable/least expensive why I can use to find out
if the db is alive (other than a select stmt to a user table)? Least expensive, I meant, I have to run this constantly, should cost
very little when the db is alive and connected.
As specified in this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;138541 you can use ODBCPING utility to check the server status and its ideal to pass the select query to check the database status. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

We use ADO(OLEDB), not ODBC.
Couldn’t find any ping utility from the install folder.
quote:Originally posted by satya As specified in this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;138541 you can use ODBCPING utility to check the server status and its ideal to pass the select query to check the database status. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

SQL will not terminate the connection until client disconnects or SQL services are refreshed.
Have you checked in the current activity for the status. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

If connection pooling (or resource pooling for OLEDB) is used (it’s on by default on most systems) the ADO _ConnectionPtr can point to a connection in the pool even if the underlying server is offline. You need to check the connection status of the object in the pool to determine if the server is online or offline. Here is a link on the subject (see "Connection Status"):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp For the monitoring application you could turn off pooling. And instead of running a query against a table you could just connect to the master database and do a "SELECT 1+1" or something. /Argyle
Thanks!
quote:Originally posted by Argyle If connection pooling (or resource pooling for OLEDB) is used (it’s on by default on most systems) the ADO _ConnectionPtr can point to a connection in the pool even if the underlying server is offline. You need to check the connection status of the object in the pool to determine if the server is online or offline. Here is a link on the subject (see "Connection Status"):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp For the monitoring application you could turn off pooling. And instead of running a query against a table you could just connect to the master database and do a "SELECT 1+1" or something. /Argyle

]]>