SQL Error 80004005 after many successful transactions 1

[dave755]

I have an internal use web page that uses VBScript/ASP to perform a long sequence of INSERT and UPDATE transactions - all to the same table in SQL Server 2000.

The code executes as expected for the first 1900+ transactions, but then fails with the error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. 

/DonorForms/ReadForm.Asp, line 148

The failure is after approximately the same number of successful transaction every time, but not exactly. IE and FireFox exhibit the same behavior, but at different places.

Here is the relevant code:

if nDataItem = 0 then
  sSQL = "INSERT INTO DonorForm.dbo.tblFormData (iCNS, idItem, vcValue, dtLastUpdate) VALUES ("
  sSQL = sSQL & nCNS & ", " & idItem & ", '" & sVal & "','" & Now & "')"
else
  sSQL = "UPDATE DonorForm.dbo.tblFormData SET vcValue='" & sVal & "', "
  sSQL = sSQL & "dtLastUpdate='" & Now & "' "
  sSQL = sSQL & "WHERE iCNS = " & nCNS & " "
  sSQL = sSQL & "AND idItem = " & idItem
end if
                      
cn.Execute sSQL
rs2.movenext

Dave Gee

 

[tsuji]

Hello dav755,

[1] When this phenomenon happens, run netstat, you might probably find a big number of open connection to the dbase. (If this is verified, it adds weight to the suggestion below for correcting the problem.)

[2] Run the following .reg file (or manually according to meaning of the code.)

REGEDIT4

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"MaxUserPort"=dword:000A00AE

regards - tsuji

 

[dave755]

Hi Tsuji,

Thanks for the tip. That seems to be what is happening and inserting the registry entry you provided allowed the script to finish.

My confusion though is what to do about it. The script uses only one ADODB.Connection object and four ADODB.RecordSet objects. I am scrupulous about closing things as soon as I am done with them.

Why are there such a large number of open connections to the database?

regards,
Dave

Dave Gee