how to switch to SQL server state for sessions.... I get an error

Because of some In Proc problems I try to switch to SQL State session.

I checked my CMS开发者_StackOverflow社区 database and I see the tables like aspnet_XXX already exists. To be sure I also created the DEFAULT database "aspnetdb" and compared the tables aspnet_xxx and checked whether they already exists in my CMS_DB_DEV or not... and all of them are already exists.

Then I went to web.config and I configured via IIS the sessionstate connectionstring and it looks like below:

  <system.web>
      <sessionState allowCustomSqlDatabase="true" 
                    cookieless="UseCookies" mode="SQLServer"   
                    sqlConnectionString="Server=localhost;Database=cms_db_dev;User ID=sa;Password=xxxx" 
                    timeout="20" />

now when I run my application I get the error:

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.

I use W7 pro, IIS 7.5 is installed

I checked these 2 links which mention the problem but no hope:

http://support.microsoft.com/kb/317604

http://www.brianstevenson.com/blog/aspstate-concurrently-running-for-net-1011-and-net-20#comment-984

AFter I get this working on my localhost, I'll do the same in Production machine. It seems the tables already exists, so I just need to configure web.config....

I MAY NOT DELETE THE EXISTING tables (like aspnet_xxx), because it's used by Dotnetnuke CMS system... But I can test on my localhost...

so what am I missing ?

Sounds like the production environment has perhaps only the 1.1 versions of the DB structures you need. You can run the 2.0 aspnet_regsql.exe with the -ssadd flag and appropriate arguments to create the newer version.

http://msdn.microsoft.com/en-us/library/ms229862(v=vs.80).aspx

A couple of points:

1. The SQL Session tables have names starting with ASPState, not aspnet - those are for the various provider stores (membership, roles, profiles, etc).
2. If you run aspnet_regsql with -ssadd command, it will by default create a Database called "ASPState" with some stored procedures in it, and use TempDB for the actual persistance.

To use your existing database, you would need to run:

aspnet_regsql -ssadd -sstype c -s LocalHost -d cms_db_dev -u sa -P XXXXX

This will:

• -ssadd Add support for SQLServer Mode session state to the Sql Server specified with the -s option
• -sstype c Create a custom persisted session state database, where both the data and the stored proc's are stored in the same database (as opposed to p which would store everything in a database called ASPState, or t which will create the stored proc's in the ASPState database but store the data in TempDB).
• -s Localhost (or -s .\) Use the local database instance, update as needed.
• d cms_db_dev The name of the custom database to add the tables and stored proc's to.
• -u sa -P XXXXX Valid credentials to create the database, could probably use -E to use windows credentials if on LocalHost.