Problems running a batch job

Hi, I've got several ASP programs which are looping through 1000's of records and then drilling into each of them, processing and moving on. The numner of database calls will be >100,000 through the run of the program.

Recently the jobs have started failing with and error

"Microsoft SQL Native Client: TCP Provider: Only one usage of each socket address (protocol/network address/port) is normally permitted."

I assumed I wasn't closing my recordset connections when I was done with them, but I have ascertained that this is not the issue.

Researching this it appears to be some limitation in the TCP Sockets which SqlServer allows to be opened (5000) and the time it takes to clear one once and close has been issued.

I am looking at tweaking server parameters to increase this but while playing about I found a way to make it work, and just wondered what the opinions of people here were about my technique:

I've alays opened a DB connect at the start of my page and closed it at the end of the page, using the same connection for all db work in the page. In this instance I decided to close the connect after each SQL call and reopen it before the next one... this got away from the failures, but I have no idea on the performance overheads?

Any comments?

What does a batch job have to do with asp? What does your question have to do with asp? Why would you be using asp to do what you described, you should have some other programming environment do what you're describing imho.

It's actually a way of creating a flat file for upload to some partner sites... I use ASP because I know ASP (actually I've just changed it to be vbs but same principal applies)...

Ignoring the debate about how I should be doing it any ideas about the technique required, I'm basically asking whether there is a significantly different overhead in either of the following

open connection
loop
create recordset
process recordset
close/destroy recordset
end loop
close connection

or

loop
open connection
create recordset
process recordset
close/destroy recordset
close connection
end loop

Moved from the asp forum

If I'm understanding you correctly, the simple solution to this is to not run this remotely. This should be run as a server cron.

I think you are understanding me correctly. Although this is a vbs script being run on a windows server through windows scheduler not cron.

Okay, since there is no limit to the maximum number of sequential connections, it seems as though one of two things is happening.

Most likely there is an error that is preventing your connection from being properly closed. This "connection leak" prevents your script from connecting again on the next iteration.

The second, less likely culprit may have something to do with connection pooling.

Going on the first, here's my suggestions. Avoid constructing your code like the following:

Set oConn = blah blah blah
oConn.Open
' Instruction set
oConn.Close

If there is an error in any of your instructions, it can prevent oConn from being explicitly closed. Try this approach instead:

Set oConn = blah blah blah
oConn.Open
x = 0
Do Until x=1
' Instruction set
x = 1
Loop
oConn.Close

Of course you can use any native commands to create your do loop. I just wanted to demonstrate it here. This ensures that no matter what happens, your connection is always closed again.

I thought I'd post back. You can achieve the same effect I proposed above without using a loop. This would work as well.

Set oConn = blah blah blah
oConn.Open
x = 0
If x = x Then
' Instruction set
End If
oConn.Close

Another possibility would be a With statement, but I can't find documentation to show it works.

Set oConn = blah blah blah
With oConn
.Open
' Instruction set
End With
oConn.Close