Connection issues

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

We have an application server which serves as an access point to a
database. Each user connected to the server has their own connection
to the database. Pooling is turned on, but since everyone has a unique
Workstation ID, they share with only themselves.

Lately, we have seen many strange errors come across. It looks a
stored procedure we call is returning null(No, not DBNull, but actual
dotnet null). We can see what the user is feeding as input into the
stored procedure and in any normal test, we get back the proper
information. At any given time that one user is getting this error, 49
other users can make the same call and get back proper information.
The database is Sql Server 2000 SP3a.

Has anyone seen this before? Does anyone know how to fix it/what is
wrong?

Currently, our fix(not very good), is if a user calls up and says they
have a problem, we kill their connection through enterprise manager.
They retry, get a 'General network error.' On the next try, they are
off and running with no problems. This would lead me to believe there
is an issue with the connection. We are using both SqlDataAdapter and
SqlCommands to execute the stored procedure.

Any help on this issue or debugging procedures to find more information
would be greatly appreciated. I have run a profile on the commands
being executed database side, but this doesn't seem to help.

Steve
 
Can you clarify this a bit?
Are the applications Windows Forms applications that make their own
connections or is this an ASP/IIS or XML Web Service application?
What version of .NET are you using? Are all of the users calling the same
SP? What do you mean by "returning" a NULL? Is this in the RETURN value, an
output parameter or in the rowset? Is it throwing an exception? Is the
connection held open or opened and closed as needed?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
This is a winform application creating the connections, using .NET
1.1.4322. There are multiple SPs being called, but it is typically a
set of 3 or so SPs that we are seeing the issue from. As an example,
one SP inserts a row with an identity column. That identity column is
returned to us from @@identity. There are no triggers affecting this
table we are inserting into. We execute this in a SqlCommand,
returning us an output parameter. We get no exception in the
application until we try to use this data, that we expect to be an int.
Are exceptions thrown in Sql Server? How could we find them if they
were? I saw a few errors in the Event Viewer of the Sql Server's
server.

All the connections are being left "open" by the connection pooling.
We release the connections, but because of connection pooling, the
connections are all kept around. I think that if we were to take off
connection pooling, this issue would dissappear, but we don't want to
lose the performance gains.
 
Ok, let's try to fix this.
1) Connection pooling will have no impact on performance in a Windows Forms
application if the connection remains open for the lifetime of the
application. In your case I think you could do this. It has only minimal
impact if you're constantly opening and closing connections (which is not
required in this architecture). Pooling makes sense for ASP or Web Services.

2) Stop using @@Identity and switch to SCOPE_IDENTITY(). It's safer.

3) So you're returning the new Identity value in an OUTPUT parameter? Did
you close the data stream before fetching it? This means if you aren't using
Fill to execute the code, you'll need to use ExecuteNonQuery or close the
DataReader once you fetch your rows.

4) Test for errors on the server by checking @@Error. I suspect that the SP
is having problems of some kind and is not working as intended. The fact
you're seeing errors in the log is an indication of this.

Turn off pooling, but I expect it won't solve anything--but I've been known
to be wrong from time to time... ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I will set this up with some changes and add this to our system and see
what I find out. I will add some additional logging to our application
and see what we come up with. I most likely won't have an answer until
Monday.

Answer to #3 is that we are using an ExecuteNonQuery for this.

Question on #4: Is there a good way to use @@Error in ADO.NET? Or
should I just return the @@Error as an output parameter and then log it
if it is not an empty string or null?
 
First, @@Error should be used inside the SP to detect and deal with
exceptions. The SP should RETURN a failure code to indicate that something
unexpected happened. ExecuteNonQuery would eliminate the rowset blocking
issue so your OUTPUT parameters should be hot immediately.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Steve said:
Each user connected to the server has their own connection to the
database. Pooling is turned on, but since everyone has a unique
Workstation ID, they share > with only themselves.
Click to expand...

To take advantage of pooling, you would want to use "SQL Authentication"
instead of Windows Integrated Authentication. In SQL Authentication, you
only need one credential (ID+PW). The disadvantage of SQL Authentication is
that you have to pass the ID+PW to the server from your client Windows Form
application. However, You can use DPAPI or other utility to encrypt/decrypt
the SQL credential via config file. Try that to see what happen next. I am
using this method all the time and with 50+ concurrent connections, no
problem so far. Like other posters said, test your sql Procs first on the
server before deploying them.

John
 
The SPs are tested, and I can't get it to fail, even using the inputs
from the user that is failing. They are simple inserts that should not
fail.

Are you using log4net by any chance? We noticed that log4net ADO
adapter stopped logging to our database, for no seen reason. So, we
converted this so we did the write. I don't know what is wrong, but
this is when we started having problems in our app.
 
Steve said:
Are you using log4net by any chance?
Click to expand...

No. I do my own logging: 1. To Event log and 2. To SQL Log where I write
every transaction to logfile. For critical error, I use Event log. The log
can be turned on and off via config file.

John
 
I made the changes to check @@Error and log if this is not 0. We are
seeing no logs, so it doesn't look like it is the stored procedure that
is messed up.

We will be changing the database connection to stop using thread
pooling or make the connections expire after X amount of time.
 
Back
Top