SQLServer Session State

Discussion in 'ASP .Net' started by Julie Barnet, Jan 22, 2004.

  1. Julie Barnet

    Julie Barnet Guest

    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
    Julie Barnet, Jan 22, 2004
    #1
    1. Advertising

  2. Julie Barnet

    Hermit Dave Guest

    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


    --
    Regards,

    HD
    Once a Geek.... Always a Geek
    "Julie Barnet" <> wrote in message
    news:...
    > 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, Jan 24, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. quique

    session state - SqlServer

    quique, Nov 12, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    732
    Cowboy \(Gregory A. Beamer\)
    Nov 12, 2003
  2. A

    SQLServer session state

    A, Nov 19, 2003, in forum: ASP .Net
    Replies:
    6
    Views:
    1,587
  3. John Q. Smith

    SQLServer session state performance issues

    John Q. Smith, Jan 12, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    3,442
    =?Utf-8?B?Sm9obiBRIFNtaXRo?=
    Jan 16, 2004
  4. Jeff Carver

    SessionID and SQLServer Session State

    Jeff Carver, Feb 27, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    528
    Jeff Carver
    Feb 27, 2004
  5. =?Utf-8?B?SmVmZnJleQ==?=

    Upgrading ASP w/SQLserver 2000 to ASP.NET w/ SQLserver 2005

    =?Utf-8?B?SmVmZnJleQ==?=, Apr 6, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    475
    sloan
    Apr 27, 2007
Loading...

Share This Page