Re: Connection issues

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.
__________________________________

"Steve" <fizzy1236@xxxxxxxxx> wrote in message
news:1113597314.305312.286110@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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.
>


.

Relevant Pages

  • Connection Pool Issue
    ... I'm having a problem with connection pooling from my ASP.NET Web ... Server A and Server B both are running Windows 2000 Server sp4, ... Connections are set to remain in the pool for 60 seconds. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Connection Pool Issue
    ... Max pool size 10 seems low... ... My first thought would be that connection poolling is actually disabled on A ... Server A and Server B both are running Windows 2000 Server sp4, ... all the usual places for enabling connection pooling. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Connection Pool Issue
    ... I just assumed that a low connection ... I increased the Max Pool size to 100...the problem has ... Server A and Server B both are running Windows 2000 Server sp4, ... all the usual places for enabling connection pooling. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Best Practices - Connecting to Oracle from a fat client
    ... logic is on the client, and not on a centralized server, connection pooling ... Do not open a single connection ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL SVR 2000 Custom Connection Management
    ... ADO.NET connection pooling works fine and there is no need for ... custom schemes. ... Server connection management service for an ASP.Net application they are ...
    (microsoft.public.dotnet.framework.adonet)