Solved

SQL server connection error after large amounts of conn.execute

Posted on 2004-10-13
12
676 Views
Last Modified: 2009-07-29
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
          rs2.close

          rs.movenext
      loop
      rs.close

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=127.0.0.1,2433;Connect Timeout=2;Initial Catalog=forum;Network=DBMSSOCN;User Id=sa;Password=xxxxx

Any help on this topic would be greatly appreciated.
-Bas
0
Comment
Question by:ApolloDrm
12 Comments
 
LVL 6

Expert Comment

by:masirof
Comment Utility
Alternatively,

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)
0
 
LVL 6

Expert Comment

by:masirof
Comment Utility
So looks like timeout problem..
0
 

Author Comment

by:ApolloDrm
Comment Utility
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.
0
 

Expert Comment

by:Mart76
Comment Utility
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?)
0
 

Author Comment

by:ApolloDrm
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Expert Comment

by:mbraak
Comment Utility
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.
0
 

Author Comment

by:ApolloDrm
Comment Utility
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    127.0.0.1:1027         127.0.0.1:2433         TIME_WAIT
  TCP    127.0.0.1:1028         127.0.0.1:2433         TIME_WAIT
  TCP    127.0.0.1:1029         127.0.0.1:2433         TIME_WAIT
 .. up to ..
  TCP    127.0.0.1:4998         127.0.0.1:2433         TIME_WAIT
  TCP    127.0.0.1:4999         127.0.0.1:2433         TIME_WAIT
  TCP    127.0.0.1:5000         127.0.0.1:2433         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?
0
 

Author Comment

by:ApolloDrm
Comment Utility
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!
0
 

Author Comment

by:ApolloDrm
Comment Utility
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 ..
0
 

Author Comment

by:ApolloDrm
Comment Utility
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.
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now