I am using MS SQL 2005 with classic asp. I will receive intermittent errors once in a while and on occassion some of those those errors point to a connection not closed properly. This is not a consistent problem in any one file, but seems to pop up at random here and there.
I have been investigating ways to streamline SQL and one question I can not find a definitive answer on is whether a connection needs to be closed once an asp page/script is complete. I do use multiple connections within a page and obvously close one before I go to the next, but I do remember reading somewhere that if only one connection is used, or the last connection on a page, does not need to be closed at the end of the script as it will do so automatically once a page completes rendering on the server.
The question is should I close each and every connection to sql at the end of my code, even if it does not generate errors, would this optimize performance in any small way. It's a lot of code to go through, and make sure I am closing a connection that actually did open, so before i do am wondering if this is necessary, or even marginally beneficial.
Is there a way in classic asp to check for and close all and any open connections in say the footer of the document, a generic piece of code that can be shared by all files?
Use only one single connection. Open the connection, query the data, close the connection immediately. Hold the connection open for the least amount of time as possible.
Why would you need to use more than one connection? That is a real maintenance headache. If you use more than one, and one connection is attempting to update rows, while another is attempting to select rows, you will get locking and blocking. I would bet that is where your errors are coming from. Check the SQL error log for exact description.
Since classic ASP is a more of a top down programming, you should not need more than one single connection.
Sounds good, thanks. I will look into the "locking and blocking" with greater detail through my code. The reason for multiple connections is due to multiple databases and tables. I do make sure that I have closed any one table prior to updating information in it. That logic I've managed to anticipate.
Back to my original question though - is it safe to assume that any connection left open will close upon page completion with no memory or performance penalty? Is it worth the effort of going through all code to make sure each connection is closed programmatically within asp prior to page completion?
And it is also true that you do *NOT* need to close the connection. It's a good idea, especially if you will do a lot of processing on the ASP page after you are finished with it, but yes, the ASP engine will close and release (SET conn = Nothing) all connections for you.
Horror stories about needing to close connections come from way back in version 1 or ASP, in 1995 or 1996, where indeed there were bugs. But after version 3 of IIS appeared, there has never been a need to close or release any ActiveX objects that are supplied by Microsoft (may not always be true of 3rd party objects).
Think of it.
How many times have you seen code like this:
Set conn = Server.CreateObject("ADODB.Connection")
Set RS = conn.Execute("SELECT ... WHERE ...")
If RS.EOF Then Response.Redirect "couldNotFind.asp"
Do you see any close of the connection or recordset or release of either in that code, when it does the Redirect? Nope.
And yet there must be tens of thousands--if not more--ASP pages out there written like this. In each and every case, when the Redirect occurs, ASP is correctly closing and releasing all the connections and recordsets.
Nota Bene: Does not apply to ASP.NET. I can't tell you how much time I spent tracking down an unclosed connection with an ASP.NET page (was happening because of an error that was being handled in a subroutine). There you *MUST* close connections, very very carefully. Simple reason: ASP.NET doesn't use ActiveX.