SQL server connection error after large amounts of conn.execute
Posted on 2004-10-13
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 & ")"
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.