sql timeout error rollercoaster

[tradnbillies]

Hey,

I feel that I will need a little explanation before I dive into details of my problem. I am currently interning at a company where I have been asked to build an ASP.NET application to track employees who will not be in the office for the day. The application uses your windows login authentication to verify your account is active in a SQL server database. We also use active directory to get various user information. Lately, I have been getting various errors in the application that I cannot figure out. I built an error reporter which writes the error to the system log, and emails me the exception.
Below is the exception that I get all the time, but it is usually followed by the application acting VERY funny. For example, I usually will get 3 or 4 errors at a time by a few different users, but this one error will always be present, and the other errors I get seem to come from pretty failsafe functions and boggle my mind on how they can error out. The other notable problem with these errors is that the one I am pasting below does not necessarily have a timestamp before the other errors, but I think that's because the connection hangs and it takes a few minutes to error out. Anyways, take a look at the error below, and I will include the function Is_User() as well to look at. Any comments/suggestions would be much appreciated, and I can give supply any more details that may be needed.

EXCEPTION:
===================================
An error has been encountered by the user: IAM\dpagin
Time: 3/21/2006 9:53:33 AM
The error follows:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlDataReader.InternalClose(Boolean closeReader)
at System.Data.SqlClient.SqlDataReader.Close()
at System.Data.SqlClient.SqlConnection.CloseReader()
at System.Data.SqlClient.SqlConnection.Close()
at o3.dbConnect.DisconnectIt()
at o3.class_user.Is_User()
===================================

The Is_User function follows:
===================================

[SIZE=2][COLOR=#008000]''o3_is_user
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] Is_User() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]   Try
[/COLOR][/SIZE][SIZE=2]      dbc.ConnectIt()
[/SIZE][SIZE=2][COLOR=#0000ff]      Dim[/COLOR][/SIZE][SIZE=2] myCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = dbc.RunQuery("o3_is_user")
      myCommand.CommandType = CommandType.StoredProcedure
      myCommand.Parameters.Add("@user_name", SqlDbType.VarChar).Value = userName
[/SIZE][SIZE=2][COLOR=#0000ff]      Dim[/COLOR][/SIZE][SIZE=2] reader [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataReader = myCommand.ExecuteReader
      reader.Read()
[/SIZE][SIZE=2][COLOR=#0000ff]      If[/COLOR][/SIZE][SIZE=2] reader.GetValue(0) > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]          Is_User = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]      Else
[/COLOR][/SIZE][SIZE=2]          Is_User = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
[COLOR=#0000ff]      [/COLOR][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2]      reader.Close()
      dbc.DisconnectIt()
[/SIZE][SIZE=2][COLOR=#0000ff]   Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
      myErrorHandler.RecordError(userName, ex)
[/SIZE][SIZE=2][COLOR=#008000]      'Return False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]   End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][COLOR=#000000]

[/COLOR]
[/COLOR][/SIZE]===================================
there is also an overloaded function Is_User(name As String) which takes a USERNAME as a parameter in place of the variable "userName" in this function. The variable userName comes from:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] userName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = HttpContext.Current.User.Identity.Name.ToString.Trim[/SIZE]
[SIZE=2]

[/SIZE]
Other notable functions used here are below:
===================================
part of dbConnect class, dbc is of type dbConnect
===================================

[/SIZE]
[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] myConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
[/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] ConnectIt()
[/SIZE][SIZE=2]   myConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
[/SIZE][SIZE=2][COLOR=#0000ff]   If[/COLOR][/SIZE][SIZE=2] myConnection.State = ConnectionState.Closed [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]      myConnection.Open()
[/SIZE][SIZE=2][COLOR=#0000ff]   End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Shared [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] RunQuery([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] StrSQL [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand
[/SIZE][SIZE=2][COLOR=#0000ff]   Dim[/COLOR][/SIZE][SIZE=2] myCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand(StrSQL, myConnection)
[/SIZE][SIZE=2][COLOR=#0000ff]   Return[/COLOR][/SIZE][SIZE=2] myCommand
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE]

===================================





Thanks for any help!!!

-Dave

 

[tradnbillies]

Does anyone have any thoughts at all...?

 

[Paszt]

Hello tradnbillies and welcome to the forum.

The error message states that the error occurred while trying to close the connection, in the DisconnectIt procedure. Show that procedure.

The System.Data.SqlClient.SqlCommand.ExecuteNonQuery function may be of interest to you in this situation. The function executes an SQL statement against the connection and returns the number of rows affected. Therefore if this function returns a value greater than zero given the SQL statement you've used, the user exists.

Please post code in code tags to help make it more readable, see my signature for a link.

 

[tradnbillies]

Here is the disconnectIt procedure:

[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Shared [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] DisconnectIt()
[/SIZE][SIZE=2][COLOR=#0000ff]  If[/COLOR][/SIZE][SIZE=2] myConnection.State = ConnectionState.Open [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]     myConnection.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]  End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]  myConnection.Dispose()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

I will change the isUser() to use ExecuteNonQuery and see if that helps anything. Thanks for the help. Let me know if you see anything wrong in this function. Thanks.

-Dave

 

[tradnbillies]

Paszt, or anyone else for that matter...

Any thoughts once I posted the DisconnectIt() method.

-Dave

 

[tradnbillies]

After looking at some more of the errors I have been receiving, I have some more code to post that could possibly result in the errors I'm getting. I notice that a handful of times the procedures that are calling DisconnectIt() are using: SqlCommand.ExecuteReader(CommandBehavior.CloseConnection) and then calling DisconnectIt().
Example:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = New SqlCommand(storedProcedureName, dbc.myConnection)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] reader [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
reader.Read()
[/SIZE][SIZE=2][COLOR=#0000ff]   'more code[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2]reader.Close()
dbc.DisconnectIt()[/SIZE]

When this happens, I'll typically see two errors that follow:

Error1:
System.NullReferenceException: Object reference not set to an instance of an object.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at o3.class_user.aaEditDeptReq(Int32 dept_id, DateTime monthYear)

Error2:
System.InvalidOperationException: Internal connection fatal error.
at System.Data.SqlClient.SqlDataReader.InternalClose(Boolean closeReader)
at System.Data.SqlClient.SqlDataReader.Close()
at System.Data.SqlClient.SqlConnection.CloseReader()
at System.Data.SqlClient.SqlConnection.Close()
at o3.dbConnect.DisconnectIt()
at o3.class_user.aaEditDeptReq(Int32 dept_id, DateTime monthYear)

I am guessing I need to get rid of the commandBehavior.CloseConnection and just call ExecuteReader w/o parameters, but can someone explain why this is happening? I'm not 100% sure if all of these errors are related, but I feel like this is a pretty good hunch on part of the problems I am having.



Thanks,

Dave

 

[shroomy]

have you looked at the database (with enterprise manager) and looked at the number of connections per process/database

Ill bet you have a connection pooling problem.

Let us know how many active connections are hanging around for your db.

Ill also post some better wrappers for your connections when I get to work tomorrow.

 

[tradnbillies]

I'm not exactly sure how I would do that... I won't be in work again until Friday, but I'll be able to look at that then and see if I can figure it out. Thanks for replying.

-Dave

 

[shroomy]

I'm not exactly sure how I would do that... I won't be in work again until Friday, but I'll be able to look at that then and see if I can figure it out. Thanks for replying.

-Dave

in enterprise manager there is a folder for databases, DTS, management, etc ..

open (+) the management folder
open the current activity folder
open the Locks/object

if you see a bunch of processes in the right side, you are doing something wrong in code that is causing the connection to not be released and will eventually run into trouble.

be sure to do this after people are using your app, not first thing when no one is using it or obviously there will be no connections.

 

[tradnbillies]

Well, I noticed there were a GOOD handful (maybe 20) of the connections under both Process Info, and Locks/Object. They went away when I closed my browser as well. Now, is there a setting on that database that would only allow so many connections to this database? I suppose there would be something in my code that is creating all these connections, but why can the database not handle it. Would my problem be: in EVERY function, I create a new SqlConnection object to execute the command. Should I be using just one SqlConnection object? Any help is much appreciated.

 

[shroomy]

You should have a dbwrapper class that you can pass a string to and return a dataset or datatable or whatever. (just slightly diff if your using stored procedures)

that way all your data access is in one spot.

you should still open and close the connectioin after every call, .net will pool the connections for you.

and its easy to do thing in an order that causes the connection to not release when its done.

Ill try to remember to get you an example tomorrow of a good way to do it.