This morning, we were attempting to test an existing app on a server where we had recently made some AppPool and other changes to IIS (installing a newer application to run side-by-side). When we tried to run the old app (which was working perfectly before), we received this big, ugly error:

Scratching our heads, we went back to the database server, verified that the database was online and functional, that the name was resolving correctly, that the proper ports were open, and that it could be reached from the app server. No problems. I even wrote a script to validate that from the app server we could successfully connect to the ASPState database on the test server using the same credentials. No problem. I ran a trace looking for login failures or stored procedure calls that may have been choking up ASP.Net. Nothing. I was convinced that the application was using the wrong config or otherwise wired to try to connect to some other database server that didn't have ASPState or wasn't online. Co-workers assured me that this was not the case.

So I expanded my trace to include the Audit Login event (previously I was only expecting to see login failures). Sure enough, the app *was* attempting to log in to the ASPState database, and then promptly barfing the verbose error message above. Which sounds very contradictory, but I suspected the error message was just a generic error condition and that the root cause was buried deeper in what ASP.Net found once it connected to the database.

I expanded my trace yet again, adding the SQL:StmtCompleted event. (I usually don't think to use this event for debugging applications because we have a very strict policy of not using ad hoc SQL. I guess Microsoft still thinks ad hoc SQL is okay.) Sure enough, I saw this come through right before the exception:

Select name from sysobjects 
  where type = 'P' and name = 'TempGetVersion'

Apparently, somewhere deep in the bowels of the Framework, SQL session state management has been updated to include this call, I guess to ensure you are not using an antique version of ASPState (which we are, since this object did not exist). I am not sure if this update came from updating IIS, Framework updates from Windows Update, or some combination. But I was rather reluctant to update the environment by ripping down ASPState and installing a completely different version. Here is the workaround I came up with:

I located the most recent version of the script InstallSQLState.sql, which gets placed onto your machine with any .NET Framework updates (in my case, this was in C:\Windows\Microsoft.NET\Framework64\v4.0.30319\). I found the reference to the object dbo.TempGetVersion and, since it does the same thing as an existing object (dbo.GetMajorVersion), I created this wrapper:

USE ASPState;
GO
 
CREATE PROCEDURE dbo.TempGetVersion
    @@ver int OUTPUT -- ooh, bad variable name
AS
BEGIN
    SET NOCOUNT ON;
    EXEC dbo.GetMajorVersion @@ver = @@ver OUTPUT;
END 
GO

Once this procedure was created, the application happily started working. Hooray!

Well, hooray for a few minutes, anyway. We still need to do thorough testing to ensure that I don't actually *need* the functionality in the new version of the database. And it is probably a good idea that we plan to run the upgrade anyway – coming back full circle, it turns out that the error message is technically correct, and that the 2.0 version of the database was not installed. But to the average user, this is not as obvious as the clever error message author likely thought it was. Why can't the 2.0 version of the database be installed using a different name other than ASPState? I think it's reasonable to expect that people will be running multiple applications or sites on their servers, and nervous about forcing every single application to use a (to them, at least) new, untested version of the schema. And really, I should be able to have multiple independent copies of ASPState on the same instance – the database name should be an argument to the app just like the server/instance name and credentials.

So again, while the above "fix" isn't the best approach and is not permanent, it was the quickest. This little update allowed us to proceed to the steps of actually testing the app… and I hope it prevents someone else from chasing their tail and blaming the wrong people or code. We spent a long time today looking in the wrong places for the wrong problem.

Now, off to find out if ASPState 2.0 is fully backward-compatible… and maybe file a Connect item about that error message.