This project has moved. For the latest updates, please go here.

Use with MSSQL Server Express, without the SQL Agent Service?

Jul 6, 2014 at 1:43 AM
Edited Jul 6, 2014 at 1:44 AM
Hi,

This little component is fantastic and completely fills a hole in a current release I am working on. My big question is: I am using the tempdb on a local instance of SQL Server. On the production server, I have SQL Server Express running, which does not have SQL Agent, and is not an option unless you have a Standard install.

I have this service running and working, but I know the job to clear sessions is not going to run. I tried everything I could think of to see the data in the tempdb, to write a small program to clear sessions myself, but I cannot find the table dbo.Session anywhere on the server?

Any suggestions?
  • Marlian Smith
Coordinator
Jul 8, 2014 at 12:00 PM
Hello,

If you did not change the database where session are stored in your AspSessionServiceConfig.xml and AspSessionServiceConfigAdo.Net.xml, you should see the session information with:
SELECT *  FROM [tempdb].[dbo].[Session]
Please:
  1. Make sure to connect to the right SQL Server Express instance and the you are checking the right database (check your AspSessionServiceConfig.xml and AspSessionServiceConfigAdo.Net.xml).
  2. Note that every time you restart Sql Server (ex: computer reboot) the Session table will be drop. When you first call a page that uses the Session service the table will automatically be recreated.
If you don't have the Sql Agent you would need another way to do on a regular basis what the job does.

Here is the job query:
IF OBJECT_ID (N''[dbo].[Session]'', N''U'') IS NOT NULL
DELETE [dbo].[Session]
WHERE Dateadd(minute, [SessionTimeOut], [LastAccessed]) < GETUTCDATE()
You can simply use a windows scheduled task that run a sqlcmd.exe command and run the above query every minute.

I hope this help
Yanal
Marked as answer by Yanal on 7/17/2014 at 9:36 AM
Jul 8, 2014 at 10:59 PM
Edited Jul 8, 2014 at 10:59 PM
Hey Yanal,

I realized right before you responded that my config settings were not the administrative local account that I was using in SSMS. Once I realized that the scripts you provided above that I already had found in the install file, I logged in as that user and saw the current sessions.

I don't know why I didn't remember about user log in scope and the temporary database :) I will have no problems setting up a garbage collection service for the temporary database, as you said.

This is a totally awesome bit of functionality you put together and thank you very much for sharing!

Once last question. Where does the service pull the Session Timeout value from? Is there a way for me to set the minutes (I need several hours of timeout) for the value it writes into the database? Or should I just offset that with the update to dump sessions?

I didn't see anything in the documentation about that...
  • Marlian
Jul 11, 2014 at 3:33 AM
Edited Jul 11, 2014 at 6:38 AM
Hey Yanal,

So I have everything working and I thought everything was as it should be, except I am getting completely random timeouts. Sometimes and hour, sometimes five minutes. I have this running on Windows Server 2012, IIS 8 and a local instance of SQL Server Express 2012.

What is weird is that it looks like the same session is be caught again, but references to Session in classic ASP aren't returning. Or it just creates a new session. No matter what browser I am using, as well.

Any idea what could be causing this?

So - an update. After analyzing what's going on, it looks like the session just isn't writing the elements back to the server. It grabs the same record like it is supposed to, but just has an empty <session></session> tag. It's almost like the database connection is timing out before it writes back to the session table. I am at a loss!
  • Marlian
Coordinator
Jul 11, 2014 at 5:32 PM
Edited Jul 11, 2014 at 7:06 PM
Hello Marlian,

The session timeout can be set programmatically.

For instance in classic ASP:
AspSessionService.Timeout = 2

Set the timeout to 2 minutes
then a quick look in the database shows:
SessionId LastAccessed SessionTimeOut
D2CFE6D2-B365-48F9-BFCF-BE4AE00CEB53 2014-07-11 17:18:28.463 2

The Session will expire 2 minute after 2014-07-11 17:18:28.463 except if the session is accessed again.

Could you provide me a sample code you are using so that I can reproduce your issue?

Yanal
Coordinator
Jul 17, 2014 at 10:31 AM
Hello,

Did you find a solution?

Yanal
Jul 17, 2014 at 3:40 PM
Hey Yanal,

Sorry about not responding, just found the other response in my junk mail folder!

So I got everything working smoothly. I may be, in my server environment:

- Windows Server 2012 Standard
- IIS 8
- SQL Server 2012 Express

That default values for session timeout are not honored. That being said, I explicitly set Session.Timeout = n in the include file for the classic ASP pages (after overriding the Session object), and moved the database to an active custom database instead of the tempdb. Now everything is running smoothly. I can keep session as long as I see fit!

It also seemed to speed up performance dramatically utilizing the custom database for session storage. So I might add that suggestion to the documentation when it comes to the newer server environments - make sure you set your session timeout! (and not in global.asa :)

Your little DLL is pretty awesome and has set the platform for future conversion of my classic ASP code. You rock!

- Marlian Smith




From: "Yanal" <[email removed]>
Sent: Thursday, July 17, 2014 6:31 AM
To: [email removed]
Subject: Re: Use with MSSQL Server Express, without the SQL Agent Service? [sessionservice:550701]


From: Yanal

Hello,

Did you find a solution?

Yanal

Coordinator
Jul 17, 2014 at 4:35 PM
Hello Marlian,

You are right, you cannot use global.asa because I never found how to make session service work with global.asa.

I am glad you successfully integrates it to your solution.

If session service was useful to you I would appreciate a review (https://sessionservice.codeplex.com/releases/view/122226#ReviewsAnchor)

Yanal