Solved

SQL server connection error after large amounts of conn.execute

Posted on 2004-10-13
12
678 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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
 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

9 Experts available now in Live!

Get 1:1 Help Now