Solved

SQL server connection error after large amounts of conn.execute

Posted on 2004-10-13
12
677 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
ID: 12297503
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
ID: 12297548
So looks like timeout problem..
0
 

Author Comment

by:ApolloDrm
ID: 12299439
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
ID: 12301871
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
ID: 12305856
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:mbraak
ID: 12308197
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
ID: 12308598
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
ID: 12308623
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
ID: 12311346
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
ID: 12312249
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
ID: 12765183
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

21 Experts available now in Live!

Get 1:1 Help Now