I've spent many weekends looking into this problem and thought I'd best blog it so some one else at least gets the benefit of it.
The issue I am talking about centres around the following error message.
"The connection cannot be used to perform this operation. It is either closed or invalid in this context."
The above error message has been a true bane to me. It was an issue on a classic ASP site that was quite happily ticking away for many years. I spent ages looking through the code ensuring that the SQL Connection was properly closed after each use and that ADODB.Recordsets were being used correctly. The error didn't make sense to me because the problem only happened occasionally and I was convinced it was either an issue with MDAC or the version of IIS (we had moved to a new server a few months ago) .
The solution To cut a long story short, the solution I discovered was in SQL Server 2005! Looking through the SQL server logs I discovered that after the last request SQL Server would "Auto Close" the connection and release resources. When the website made another request SQL would be busy spinning up which would then return the error above!
To stop this happening right click on your database in SQL Server Management Studio, select properties, then select Options and set "Auto Close" to false. I believe this option is now removed in newer versions of SQL Server.
Welcome to my blog, I'm a bit of a technology geek and have been in the coding/ developer/ consultancy arena for over 10 years.
I love coding, solving problems and tinkering with new technology - you could say I am passionate about technology :)
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in anyway.
Page rendered at Thursday, 23 February 2017 04:51:07 (GMT Standard Time, UTC+00:00)