SQL server connection error after large amounts of conn.execute

I have an ASP script that marks forum messages as 'read' for a specific user (the variable userid is already set). It basically looks like this:

      rs.Open "select * from tbl_forummsg",conn,3,1            'select all forum messages (5000 records or so)
      do until rs.EOF     'loop for all forum msg

          rs2.Open "select * from tbl_forummsg_read where msgid = " & rs("id") & " and userid = " & userid,conn,3,1    'check if the msg is read
          if rs2.EOF = true then       'the msg has not been marked 'read', so insert a record in the table to mark it 'read'.
              conn.execute "insert into tbl_forummsg_read (status,msgid,userid) values (1," & rs("id") & "," & userid & ")"
          end if


When running the script it works fine, but somewhere halfway (after it has done already thousands of messages) it stops with this error:

" Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. "
With the line number pointing to the conn.execute statement.

I realize this script will cause a LOT of recordsets being opened/closed (rs2) and a LOT of conn.execute being done to insert new records, and i realize the code can be greatly improved. This is not my issue though. What i really would like to be answered is: what causes this error to happen. Even when this script causes high load on the webserver/sqlserver (on the same machine) it should be able to just complete the job and not halt execution somewhere on the way, right?

The connection to the DB has been opened with this string:

Provider=SQLOLEDB;Data Source=,2433;Connect Timeout=2;Initial Catalog=forum;Network=DBMSSOCN;User Id=sa;Password=xxxxx

Any help on this topic would be greatly appreciated.
Who is Participating?
PAQed with points refunded (500)

Community Support Moderator

Set Dataconnection = Server.Createobject ("ADODB.Connection")
Dataconnection.open "DRIVER={SQL Server};SERVER=sourcename;DATABASE=databasename;UID=userid;PWD=password"
Dataconnection.CommandTimeout = 300

Set objRSrep = Server.CreateObject("ADODB.Recordset")
objRSrep.Open SQLrep, Dataconnection, , AdLockOptimistic

(lost source)
So looks like timeout problem..
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

ApolloDrmAuthor Commented:
I tried it your changes (the .CommandTimeout = 300 and the rs.open SQLrep, conn, , AdLockOptimistic).

The first run iof the script went OK now. After repeating the script for another userID, it halted again, this time with the error:

Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired

pointing to the same line (conn.execute). Reloading the script a second time produces the same error as before:

Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
As you said, this can be improved by only creating one query that does all you want to achieve:

(pseudocode, might not be exacly correct)
INSERT INTO tbl_forummsg_read (status,msgid,userid)
SELECT 1, msgid,[userid] from tbl_forummsg
WHERE msgid NOT IN (SELECT msgid from tbl_forummsg_read WHERE userid = [userid])

I know this is not a solution but a workaround for the problem. But this should be the way to do it and will cost you less resources on the server.

-Marten (don't I know you?)
ApolloDrmAuthor Commented:
The code can be improved, this is not the issue though. The script in its current form should not time-out or give SQL server connection errors in my opinion.

To my knowledge, timeouts can occur in two ways in this scenario:
- ASP script executes too long (and it timeouts). This can be avoided by the server.scripttimeout setting.
- an SQL statement takes too long to execute (and it timeouts). This should be avoided by the conn.CommandTimeout setting.

Though the SQL timeout should not really be an issue at all. I am not sending complex large SQL statements. I'm just sending an awful LOT of very small, independant SQL statements, one after another. So in my opinion, the server should just process them one by one, but it does not, after having opened/closed a recordset (rs2) and sending an insert statement (conn.execute),  a few thousand times, it stops. I really like to know that makes it stop right there instead of continuing with the remaining 2 or 3000 repeations.
I'm currently investigating your problem.
The insert statement will open lots of connections to the database.
It seems that something is limiting the count of connection or better (the tcp port range) to 5000

Thats why no more then 5000 connections can be made.
At the moment it reaches 5000 connections the problem is there.

So still investigating where te limit is set.
I think it's an IIS6 or Windows 2003 setting.
ApolloDrmAuthor Commented:
I have checked your scenario, and can confirm that. Doing a "netstat -an" shows the server used up all of it's outgoing TCP connections, which is amounting to about 4000 (instead of 5000).

  TCP         TIME_WAIT
  TCP         TIME_WAIT
  TCP         TIME_WAIT
 .. up to ..
  TCP         TIME_WAIT
  TCP         TIME_WAIT
  TCP         TIME_WAIT

 I've cross-referenced this to the amount of 'insert' sql statements being made (through conn.execute), this is the very same amount. So for each conn.execute the IIS server opens a TCP connection to the SQL server, is this normal behaviour?
ApolloDrmAuthor Commented:
I'd like to add, not having outgoing connections available, and thus, not being able to connect the 5001st time to the sql server, does perfectly well explain the original error message "access denied or sql server not found". I think you've at least put the finger on the cause of the problem, thanks!
ApolloDrmAuthor Commented:
I've found some answers here: http://support.microsoft.com/default.aspx?kbid=328476

I've put the MaxUserPort and TcpTimedWaitDelay into the registry, which deals with the limit of port range 1024-5000. Now it goes up to 655xx, and the TIME_WAIT status is shortened to only 30 secs, so sockets are much larger available in numbers, and used sockets disappear much faster.

In the KB article it states:
" Note that the MaxUserPort and TcpTimedWaitDelay settings are applicable only for a client computer that is rapidly opening and closing connections to a remote computer that is running SQL Server and that is not using connection pooling. For example, these settings are applicable on an Internet Information Services (IIS) server that is servicing a large number of incoming HTTP requests and that is opening and closing connections to a remote computer that is running SQL Server and that is using the TCP/IP protocol with pooling disabled. If pooling is enabled, you do not have to adjust the MaxUserPort and TcpTimedWaitDelay settings. "

Clearly suggesting that my server does not use connection pooling, though i can't figure out what's causing it not to use connection pooling ..
ApolloDrmAuthor Commented:
It seems changing the connectionstring to use ODBC (made a system DSN on the server) instead of SQLOLEDB enables connection pooling .. Now the script runs without opening all those TCP sockets.

Why SQLOLEDB does not use connection pooling is beyond my comprehension.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.