A quick workaround to a misleading ASPState problem
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:
System.Web.HttpException: Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above. The connection string (server='TestServer', database='ASPState') was returned by an instance of the IPartitionResolver type 'IonMxCore.SessionStatePartitionResolver'.
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 VBS 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 general 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 practice for a variable name; not my choice! 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.
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.
After looking into this a little more, I think I mislead myself into the solution. The script files have SET QUOTED_IDENTIFIER OFF in the top, so the double quotes around the 2 in the CREATE PROCEDURE for TempGetVersion would not have been the problem. I've since rerun aspnet_regsql and it sets the .net 4.0 session state database up correctly. My problem in the beginning was probably that I was using the wizard, which if i would have read correctly, doesn't set up the session state database. For that, you have to run the command line. (DOH!)
Nothing to see here folks. Move along.
Found the problem. Microsoft provides the .sql files used to generate the ASP State database in the folder you referenced above (C:\Windows\Microsoft.NET\Framework64\v4.0.30319\). If you take a look at the two files named InstallSqlState.sql and InstallSqlStateTemplate.sql, search for "TempGetVersion". Eventually you'll get to the CREATE PROCEDURE state for "TempGetVersion", which turns out to be exactly what GrumpyOldDBA copied above. Yes, there are double quotes in there! (UGH!) I made the modification to use single quotes in both files and tried to rerun aspnet_regsql.exe, but the .exe apparently does not use either of these as it's source scripts. (Maybe it's compiled into the .exe. I didn't research this any farther.) So, I just made the changes following the commented instructions at the top of InstallSqlStateTemplate.sql (we name our database something different besides "ASPState") and ran the script on a newly created database. Everything works! We are submitting a bug to MS. Thanks, Aaron for your research!
GrumpyOldDBA, that looks suspicious, for several reasons:
1. the output variable is @ver instead of @@ver;
2. the syntax is not in line with the other objects in ASPState; and,
3. for every version of the proc that I've seen, it uses the actual major version from @@VERSION. That could never be "2"…
It looks like someone just created the procedure with any old syntax simply because it was missing.
Have you validated that session state is working for your app? It's possible that you're not using it and that it would fail if you tried.
(Of course, it is also entirely possible that the presence of the procedure is all that's necessary, and that returning a value of "mort vs. splunge" would also work. I've already spent way too much time on this issue to justify investigating any further.)
Hmmm I've had this proc in my database for some time, sorry don't know with which version of dot net but must be pre 3.5 I'd guess – sorry I don't do much with the database – however the proc is this which I'm not sure is quite the same as GetMajorVersion — CREATE PROCEDURE [dbo].[TempGetVersion]
@ver char(10) OUTPUT
set nocount on
SELECT @ver = "2"