HOW TO: Configure ASP.NET for Persistent SQL Server Session State Management

Article translations Article translations
Article ID: 311209 - View products that this article applies to.
This article was previously published under Q311209
Expand all | Collapse all

On This Page

SUMMARY

This article includes script files that configure Microsoft SQL Server to support ASP.NET SQL Server mode session state management so that session data is persisted outside the tempdb database. This article includes information to download these alternative script files, which are named InstallPersistSqlState.sql and UninstallPersistSqlState.sql.

Note The downloadable scripts that are referenced in this article are only intended for use with the Microsoft .NET Framework 1.0. You must not use these scripts with the Microsoft .NET Framework 1.1 because the .NET Framework 1.1 does not support them. Use the persistent scripts that are supplied with the .NET Framework 1.1 instead when you develop applications by using the .NET Framework 1.1.


Requirements

  • Microsoft Windows XP, Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, or Microsoft Windows 2000 Advanced Server
  • Microsoft .NET Framework
  • Microsoft Internet Information Services (IIS)
  • Microsoft SQL Server

Description of the Technique

When you use the original SQL Server mode session state management script files (InstallSqlState.sql and UninstallSqlState.sql), the ASPStateTempApplications and the ASPStateTempSessions tables are created in the tempdb database to store the session data. However, the session state data is lost if you restart the computer that is running SQL Server.

The persisting versions of the scripts (InstallPersistSqlState.sql and UninstallPersistSqlState.sql) resolve this problem by creating these tables in the ASPState database instead. Therefore, the session data is retained after you restart the computer that is running SQL Server.

To obtain the InstallPersistSqlState.sql and the UninstallPersistSqlState.sql files, download the PersistSqlState.exe self-extracting executable file. The following file is available for download from the Microsoft Download Center:
Collapse this imageExpand this image
Download
Download PersistSqlState.exe now
Release Date: April 23, 2002

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

Configure Persistent SQL Server Mode Session State Management

To run the InstallPersistSqlState.sql script file that configures persisting SQL Server mode session state management, follow these steps:
  1. If you previously used the original InstallSqlState.sql file to configure SQL Server mode session state, you must run the original UninstallSqlState.sql file to remove SQL Server mode session state.
  2. In SQL Query Analyzer, click Open on the File menu.
  3. In the Open Query File dialog box, click InstallPersistSqlState.sql, and then click Open.
  4. Click Execute on the Query menu to execute the script file.

Remove the Persistent SQL Server Mode Session State Management Configuration

To run the UninstallPersistSqlState.sql script file that removes the persisting SQL Server mode session state management configuration, follow these steps:
  1. In SQL Query Analyzer, click Open on the File menu.
  2. In the Open Query File dialog box, click UninstallPersistSqlState.sql, and then click Open.
  3. You must stop the w3svc service before you execute the UninstallPersistSqlState.sql script. To stop the w3svc service, follow these steps:
    1. Click Start, and then click Run.
    2. Type cmd in the Open box, and then click OK.
    3. At the command prompt, type net stop w3svc. You receive a confirmation that the w3svc service has been stopped.
  4. In SQL Query Analyzer, click Execute on the Query menu to execute the script file.
  5. Restart the w3svc service after the uninstall is complete. To do this, type net start w3svc at a command prompt.

Troubleshooting

  • If you do not stop the w3svc service before you execute the UninstallPersistSqlState.sql script, you may receive the following error message:
    Cannot drop the database 'ASPState' because it is currently in use
  • If you notice that the entries in the ASPStateTempSessions table are not removed after the related sessions have exceeded their expiration, make sure that SQL Server Agent is running. This functionality is implemented through stored procedures that are scheduled through jobs in SQL Server. SQL Server Agent manages these jobs.

REFERENCES

For additional information about how to run default scripts that configure SQL Server for ASP.NET SQL Server mode session state management, click the article number below to view the article in the Microsoft Knowledge Base:
317604 HOW TO: Configure SQL Server to Store ASP.NET Session State
For additional information about the net start and the net stop commands, click the article number below to view the article in the Microsoft Knowledge Base:
236166 Using NET STOP and NET START Commands to Force IIS Services to Re-Read the Registry
For additional information about ASP.NET and ASP.NET state management, click the article numbers below to view the articles in the Microsoft Knowledge Base:
305140 INFO: ASP.NET Roadmap
307598 INFO: ASP.NET State Management Overview
For more information about ASP.NET session state management, see the following article from the Nothin' but ASP.NET column on MSDN:
ASP.NET Session State
http://msdn2.microsoft.com/en-us/library/ms972429.aspx

Properties

Article ID: 311209 - Last Review: March 27, 2007 - Revision: 6.9
APPLIES TO
  • Microsoft ASP.NET 1.0
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
Keywords: 
kbdownload kbhowtomaster kbstate KB311209

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com