SQLServer Session State

[Julie Barnet]

Hi,
I am having a problem with my session state through sql server. This
used to work, then all of a sudden (the only change was setting up
replication in the sqlserver database) it stopped working.

I ran uninstallsqlstate.sql after stopping w3svc and then restarted
the w3svc and rean installsqlstate.sql. This works fine on my machine
and my session variables work again....but I'm an administrator on the
database. I go to another user, and I get execute permission problems
on ASPState database stored procedures:
TempGetAppId
TempInsertStateItemShort
TempInsertStateItemExclusive

I have never had to give permissions on the ASPState database before.
But I went ahead and tried giving permissions on these stored
procedures. Now nothing works, my link going into my asp.net page no
longer works. It just tries forever to open the page and comes back
with web service not available.

So I uninstall and reinstall the ASPState database as done before, and
same problem, permissions, give permissions, locking up. I seem to be
at a standstill.

I'm using sqlserver 2000. Any ideas would be GREATLY appricated since
I no longer have any ideas on how to fix this.

Thanks
Julie Barnet

 

[Hermit Dave]

okay here it goes... installsqlstate uses tempdb to store session state
info.
now why would you want replication on tempdb ? (plus it adds a guid column
to all tables. This could potentially break your stored proc.
there is another set of scripts which creates a seperate db for sql server
session state.
use that script..
(from msdn)
How is the database created? ASP.NET provides two pairs of scripts to
configure the database environment. The scripts in the first pair are named
InstallSqlState.sql and UninstallSqlState.sql and are located in the same
folder as the session state NT service. They create a database called
ASPState and several stored procedures. The data, though, is stored in the
SQL Server temporary storage area-the TempDB database. This means that the
session data is lost if the SQL Server machine is restarted.

To work around this limitation, use the second pair of scripts. The names of
the scripts are InstallPersistSqlState.sql and UninstallPersistSqlState.sql.
In this case, an ASPState database is created, but its tables are created
within the same database and as such are persistent. When installing the SQL
Server support for sessions, a job is also created to delete expired
sessions from the session-state database. The job is named
ASPState_Job_DeleteExpiredSessions and runs every minute. Note that the
SQLServerAgent service needs to be running in order for the job to work.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/aspnetsessionstate.asp

if you do want replication across the db (make sure you do a compare between
the user rights and the structure of objects (tables / stored procs) before
an after settting the replication