10/17/2003 NT26 and ASP pages

In the meantime, client's should not be seeing 4 hour blocks of downtime as a result of this issue. We are monitoring 24/7 and normally respond within 30 seconds of ASP going down.

[antic]

Originally Posted by Brangwyn

You may still encouter the odd error with that connection string, but it will be a lot more reliable than anything other connection string.

Why should there be problems with the OLEDB connection string? Or do you mean because the server itself is still unstable?

[Brangwyn]

We've had pleanty of issues on and off with OLEDB connections be it connection limits or what not, it still can happen. Sometimes it's server related sometimes not, but anything with 8 service packs shows ya that it may not have been the best code to begin with

[dmark]

I didn't mean that the my pages were down for 4 hours straight, but that my app validates users on every page and for the 4 hours I tried to do anything, it would time out on random intervals and made it impossible to get anything done. I agree that the response time has been fast to recover the server, but it's the amount of times that it crashes has been the problem for me.

[admin]

I wanted to follow-up with everyone on this and provide the additional details that microsoft has on this issue:

The basic problem that you are running into is that Access/Jet when running in a multi-threaded environment does not work reliably. There are multiple reasons why you will see intermittent issues when using Access/Jet in this environment.

The first issue is that you should not use the Microsoft ODBC Driver for Access in an ASP application. The ODBC driver was written years before IIS was developed therefore the two do not work reliably together. We know that there are threading issues with the Microsoft ODBC Driver for Access when it is used in ASP applications. Please see the following for more information.

"299973.KB.EN-US ACC2000: Using Microsoft Jet with IIS"

found at: http://support.microsoft.com/default...B;EN-US;299973

We strongly recommend that you use the Microsoft OLEDB Provider for JET if you must use Access in an ASP application. Please keep in mind that there are many other issues that occur when using Access in this environment and even if you move to the OLEDB Provider you may still experience errors. At the very bottom of this mail I have included links that describe some of the issues that you may encounter.

This brings me to my next point; we are aware that many customers use Access as the backend of their ASP applications. This can be done if there is low traffic on the web site, if the application is not mission critical and if the application is coded according to our recommended best practices. Again I would not recommend that Access be used as the back-end database even if the application is coded directly if you need this application and the server that hosts it, to be available 24X7. Access was not intended for 24X7 support and it will fail.

"258939.KB.EN-US Recommendations for Connecting to Databases through Internet Information"

found at: http://support.microsoft.com/default...B;EN-US;258939

I would also recommend that any customers using Access consider the following articles as they may indicate some of the problems that are likely when using Access in this environment.

"306518.KB.EN-US INFO: Troubleshooting Guide for 80004005 Errors in Active Server Pages"

found at: http://support.microsoft.com/default...B;EN-US;306518

"251254.KB.EN-US PRB: "Disk or Network Error" or "Unspecified Error" Returned when Using"

found at: http://support.microsoft.com/default...B;EN-US;251254

"253604.KB.EN-US PRB: Microsoft Access Database Connectivity Fails in Active Server Pages"

found at: http://support.microsoft.com/default...B;EN-US;253604

"175671.KB.EN-US PRB: 80004005 ConnectionOpen (CreateFile()) Error Accessing SQL"

found at: http://support.microsoft.com/default...B;EN-US;175671

"306269.KB.EN-US PRB: Error 80004005 "The Microsoft Jet Database Engine Cannot Open the"

found at: http://support.microsoft.com/default...B;EN-US;306269

[antic]

Thanks for this Brad, lots of very useful info there.

In case it's relevant, here's something I thought up to limit connections to an Access database, which may improve stability of an app. Again, this is obviously a low-traffic solution, and I haven't tested it at all, it's just a thought.

Basically, one uses an Application variable to count the number of connections to the db. When you create a new connection (usually at the start of the script) you increment this number. Use the Lock method before you increment it, and if the total is less than the desired maximum, use Unlock. If you've reached the maximum, don't Unlock straight away, but use Unlock after you close the connection object again (at the end of the script) and decrement the counter.

Used like this, the Lock method will basically put other threads on hold until the Application object is unlocked again, providing a useful "stop/go" mechanism for simple, low-traffic management of Access connections.

Application.Lock
Application("ConnCounter") = Application("ConnCounter") + 1
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "... OLEDB connection string ..."
If Application("ConnCounter") <= Application("MaxConnections") Then Application.Unlock

...

cn.Close
Set cn = Nothing
Application("ConnCounter") = Application("ConnCounter") - 1
Application.Unlock

[dmark]

I just want to make sure that us people who are using sql server that there is nothing we can do.

[admin]

No ODBC and SQL work great it just ODBC and access.

[admin]

PLEASE READ LAST POST HERE:

http://www.matrixreseller.com/forum/...ad.php?p=56100

[admin]

VOTE HERE http://www.matrixreseller.com/forum/...&threadid=6397

[bigdave]

I will rephrase 100th REPLY!! YAY!!

[Brangwyn]

That makes a little more sense