469,573 Members | 1,075 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,573 developers. It's quick & easy.

Sqlconn.open() doesn't work after starting sql server. Weird problem.

Sam
I have an application that sits in the traybar that calls a msde sp3a
database to verify that the connection to the sql server instance exists.
If the sql server is up obviously the application runs perfectly. If the
database is brought down on purpose or by a machine shutting down and the
VerifyDBConnection() function is called it fails. So far so good. Now if
the VerifyDBConnection() is called repeatedly while the database is down it
no longer connects to the database even if the database instance is
restarted
again. The exception happens on sqlConn.Open();. What makes me feel horrible
is that if the application is closed and opened again the database
connectivity runs perfectly again. This rules out any ip
address/password/network failure issues.

Why does it require that the application restart before connecting properly
again. It should connect correctly to the database as soon as the
verifydbconnection() function is called on the instance that is restarted.
I performed the test again and again maybe thinking that the datareader is
pooling connections. So I began using the sqlDataReader =
sqlCmd.ExecuteReader(System.Data.CommandBehavior.C loseConnection); . It
still does the same thing.

private bool VerifyDBConnection()

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "VerifyDBConnection");

bool bResult = false;

SqlConnection sqlConn;

SqlDataReader sqlDataReader;

SqlCommand sqlCmd;
sqlConn = new System.Data.SqlClient.SqlConnection();

sqlConn.ConnectionString = m_strConnection;

sqlCmd = new SqlCommand("SELECT getdate() as dateverify", sqlConn);
try

{

sqlConn.Open();

}

catch (System.Exception ex)

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "Exception
VerifyDBConnection SqlConnection open:" + ex.Message);

this.Cursor = Cursors.Default;

bResult = false;

return bResult;

}

finally

{

}

try

{

sqlDataReader =
sqlCmd.ExecuteReader(System.Data.CommandBehavior.C loseConnection);
if(sqlDataReader.Read())

{

bResult = true;

}

sqlDataReader.Close();
}

catch (System.Exception ex)

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "Exception
VerifyDBConnection DataReaderException");

this.Cursor = Cursors.Default;

}

finally

{
sqlConn.Close();

}

this.Cursor = Cursors.Default;

return bResult;

}


Nov 17 '05 #1
1 2285
Sam - this is a known behavior due to connection pooling. If the app is
connected to the db and the db goes down, then it goes back up the app will
need to cycle through everything in the pool (which is a fancy way of saying
you'll get a bunch of exceptions even though the db is available). In the
2.0 Framework, this will only happen once with the SqlClient provider and w/
the Oracle provider, it won't happen at all.
"Sam" <sa*@info.com> wrote in message
news:11*************@corp.supernews.com...
I have an application that sits in the traybar that calls a msde sp3a
database to verify that the connection to the sql server instance exists.
If the sql server is up obviously the application runs perfectly. If the
database is brought down on purpose or by a machine shutting down and the
VerifyDBConnection() function is called it fails. So far so good. Now if
the VerifyDBConnection() is called repeatedly while the database is down
it
no longer connects to the database even if the database instance is
restarted
again. The exception happens on sqlConn.Open();. What makes me feel
horrible
is that if the application is closed and opened again the database
connectivity runs perfectly again. This rules out any ip
address/password/network failure issues.

Why does it require that the application restart before connecting
properly
again. It should connect correctly to the database as soon as the
verifydbconnection() function is called on the instance that is restarted.
I performed the test again and again maybe thinking that the datareader is
pooling connections. So I began using the sqlDataReader =
sqlCmd.ExecuteReader(System.Data.CommandBehavior.C loseConnection); . It
still does the same thing.

private bool VerifyDBConnection()

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "VerifyDBConnection");

bool bResult = false;

SqlConnection sqlConn;

SqlDataReader sqlDataReader;

SqlCommand sqlCmd;
sqlConn = new System.Data.SqlClient.SqlConnection();

sqlConn.ConnectionString = m_strConnection;

sqlCmd = new SqlCommand("SELECT getdate() as dateverify", sqlConn);
try

{

sqlConn.Open();

}

catch (System.Exception ex)

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "Exception
VerifyDBConnection SqlConnection open:" + ex.Message);

this.Cursor = Cursors.Default;

bResult = false;

return bResult;

}

finally

{

}

try

{

sqlDataReader =
sqlCmd.ExecuteReader(System.Data.CommandBehavior.C loseConnection);
if(sqlDataReader.Read())

{

bResult = true;

}

sqlDataReader.Close();
}

catch (System.Exception ex)

{

Console.WriteLine(DateTime.Now.ToString() + ":" + "Exception
VerifyDBConnection DataReaderException");

this.Cursor = Cursors.Default;

}

finally

{
sqlConn.Close();

}

this.Cursor = Cursors.Default;

return bResult;

}

Nov 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Phillip Parr | last post: by
1 post views Thread by Richard \(MrBonus\) | last post: by
5 posts views Thread by Mike Turco | last post: by
11 posts views Thread by Dave | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.