Classic ASP / SQL Server sites intermittent connectivity

Question

We have a Windows 2003 server and we have recently started to upgrade some really old classic ASP sites that were using MS access to now use SQL Server 2005. But these sites are getting random SQL Server timeout's, which causes all of the classic ASP / SQL Server sites to go down.

Whilst these sites are down, the associated CMS for these sites, (which is built in ASP.NET, but connecting to the same SQL Server database) are fine. Also Classic ASP / MS Access sites on the same server are fine and event the site.

It seems the only to get these site back online is to restart IIS or restart the individual application pools.

There is nothing in the windows logs,

any ideas what could be causing this?

Soem more info...

The sites haven't moved server, we have only changed the db from MS Access to SQL Server...

we connect to the SQL Server db like this...

connStr = "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=hallgarthmanorUser;Initial Catalog=xxxx;Data Source=xxxx"

then most code is like this...

Set dbMain = Server.CreateObject("ADODB.Connection")
dbMain.Open connStr
Set rsPage = Server.CreateObject("ADODB.Recordset")
rsPage.Open "SELECT * FROM Pages WHERE PageID = " & PageID,dbMain,0,1
....
rsPage.close
set rsPage= nothing

Thanks kb

Does your classic ASP code properly close and set to nothing all of its connection objects, recordset objects, etc.? — Aaron Bertrand
yes the sites are very basic, pages tend to open a connection, create recordset, display data and then close/set nothing to object — kb.

D-Sect D-Sect · Accepted Answer · 2012-07-12T16:34:23

It could be possible that the old site's code is not cleaning up after itself, example: https://stackoverflow.com/a/11288637/297808

Did this work before moving servers?

Are you using an ODBC datasource for the classic ASP site's connection to the MS Access DB?

If so, are the properties the same as the old server?

Ensure that you have the latest "Jet" service pack on your server: http://support.microsoft.com/kb/239114

A few things to try...

Classic ASP / SQL Server sites intermittent connectivity

1 Answers