Recordset limit per connection?

Hi all,

I have recently moved over from SQL Server 2000 to 2005 and am now having an issue with my application with what appears to be, the number of recordsets that I can open/close on a single connection.

Here is a snippet of what I'm doing, in VB 2005 using ADO... (assuming the connection is already open and working)

Dim RS1 As RecordSet.

DIM RS2 As RecordSet

RS1.Open("SELECT...")

Do While Not RS1.EOF

count = count + 1

Console.Writeline(count)

RS2.Open("SELECT...")

..some processing...

RS2.Close

Loop

RS2 = Nothing

RS1.Close

RS1 = Nothing

Now as I said this all works fine when I connect to an SQL 2000 server but on SQL 2005 it bombs out when 'count' is approximately 1940 with an exception saying that the login failed. If I have Server Management Studio open, that connection will then freeze and throw up an error about how only one usage of each socket address is normally permitted - I think thats more a red herring though.

Any ideas? I've been through all the server settings and can not seem to find anything about recordset limits or timeouts. The only way I've been able to get around this problem at present is to open a new connection object for each iteration to be used by RS2.

Thanks everyone. Any pointers would be much appreciated.

For each RecordSet.open there can be a TCP socket open for communicating with the remote server and each socket will consume system resource and can only be cleaned up after a configurable timeout. If you open too freqently, your system resource for sockets can be exhausted and so the error message.

I recommend you to turn on connection pooling on your client.

-HTH

|||

Hi, and thank you for your response.

I am opening one connection, via a connection object and connection string that is then used by the rest of the application and passed as a parameter to the RecordSet.Open call so I thought it would only be using the one connection and not using any more ports?

As that is the case I didn't think another TCP port would be opened for each RecordSet. When viewing the active connections in SQL Server Management Studio there is only ever one open too..

|||What if you set RS2 to Nothing inside the loop, right after close. Could you try and see if this changes the behavior?|||

Another way to confirm whether it is too many socket issue, you can run the following command in a console,

"netstat -ano"

If you see lots timewait, then using pool would be good.

|||Just FYI. Connection pool is on by default it ADO.|||

Alas moving the RS2 = Nothing inside the loop doesn't help and checking the TCP/IP ports, only one connection is being open. It's very strange. I am getting around it at present by opening a new connection for each iteration. Not ideal at all.

Thanks for all your input though. I *will* get to the bottom of this!

|||

Just in case connection pooling might have been turned off using the registry, you might want to refactor your code slightly to open a connection object before the while loop then use that in the recordset open inside the while loop so for example,

Dim RS1 As RecordSet.

DIM RS2 As RecordSet

Dim cn as ADODB.Connection

set cn = new ADODB.Connection

cn.open <myconnectionstring> 'where <myconnectionstring> is your connection string

RS1.Open("SELECT...", cn)

Do While Not RS1.EOF

count = count + 1

Console.Writeline(count)

RS2.Open("SELECT...", cn)

..some processing...

RS2.Close

Loop

RS2 = Nothing

RS1.Close

RS1 = Nothing

|||Yeah that's what I am doing so I can't understand why it would fail after a certain time.. unless their is network trouble and the connection is getting dropped? I think a solution might be to try the failed login error and re-login.