Sql Azure and Sql Session Provider

Question

I'm using the guidance posted on this page

http://blogs.msdn.com/b/sqlazure/archive/2010/08/04/10046103.aspx

to enable Sql Server Session provider in my web app.  I think I followed the steps as described to get the tables setup.  However, the article neglects to mention about the required permissions that the session provider connection requires.

I added an "aspSession" user to the datareader/datawriter roles for the ASPState DB, and used that user in the connection string.  However, my web app throws an exception:

 

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.

 

In order to try and figure out what is going on, I setup the ASPState DB locally, and used profiler to determine that the Session Provider is executing

Select name from sysobjects where type = 'P' and name = 'TempGetVersion'

and receiving NO rows and thus fails

Question 1:  what are the minimum privileges that I need to grant to dbSession user/login to get this to work?

When I add the aspSession user to the dbowner role, it appears to work a bit better, but then it fails with an exception:

 

Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.

 

with stack trace:

 

[InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.] System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +1563 System.Web.SessionState.SqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) +91 System.Web.SessionState.SessionStateModule.GetSessionStateItem() +185 System.Web.SessionState.SessionStateModule.PollLockedSessionCallback(Object state) +245

 

 

Using profiler again, I can see this SQL being executed:

 

declare @p2 varbinary(7000)
set @p2=NULL
declare @p3 bit
set @p3=1
declare @p4 int
set @p4=239
declare @p5 int
set @p5=2
declare @p6 int
set @p6=0
exec dbo.TempGetStateItemExclusive3 @id=N'oz3h5z55jalbrl45ec3gjciw2014c0f1',@itemShort=@p2 output,@locked=@p3 output,@lockAge=@p4 output,@lockCookie=@p5 output,@actionFlags=@p6 output
select @p2, @p3, @p4, @p5, @p6
go
exec dbo.TempReleaseStateItemExclusive @id=N'oz3h5z55jalbrl45ec3gjciw2014c0f1',@lockCookie=2
go

 

I understand that Session state in Sql Azure is not currently supported (any posts that I've read about InstallSqlState seem to be nearly 1 year old so someone *must* have got it working by now...), but *any* pointers to getting this working would be appreciated.

Answer 1

To be honest, using the SQL Session provider likely isn't your best choice, or at least most cost affective. SQL Azure is a more expensive option then Azure storage and for something like this you don't necessarially need a RDBMS. Additionally (and I'd need to do some additional digging), SQL Azure doesn't support everything that SQL Server does. So there's an outside chance that the default session DB schema is using something that SQL Azure doesn't fully support.

That said, I'd recommend using Azure Storage session provider. There should be on in the SDK samples.

Answer 2

Brent,

Thanks for your feedback.  Allow me to clarify.  I am porting an existing hosted web application hosted on a hosting provider to Azure.  In doing this port, I am trying to minimize the amount of actual code changes that are needed in order to change environment.  Once I am up and running on Azure, *then* I can start to optimize and use the native features of Azure (including migrating to the table storage provider).

Your points about cost are well taken.  But I assure you, what we are paying our hosting provider at the moment is orders of magnitude more than the estimated Sql Azure costs.  Granted, I will reduce costs even further by moving to table storage - but that is something to think about once we are up and running on Azure.

I did look at the table storage provider, but that too seemed to cause problems within our app, and I backed out after researching and seeing posts of people complaining about that demo being buggy, slow and out of date with respect to the SDK, all of which only reinforced thoughts of minimizing code change and looking to use Sql Azure.

I am using the allegedly Sql Azure compatible scripts that were posted on http://blogs.msdn.com/b/sqlazure/archive/2010/08/04/10046103.aspx.  Having looked at the original stored procs and the ones posed on that URL, work has been attempted to "port" them to Sql Azure, removing the incompatible constructs that you mention. But the stored procedure code paths seem to fail under certain circumstances that I cannot figure out (yet).

I am VERY frustrated that there isn't a production ready "story" from MS on Session State Provider.  The demo will always be regarded with suspicion, and what chance have the public of converting Sql to Sql Azure if MS can't port their own Sql Session Provider stored procs?

Sorry for venting at you - I just need to be heard, and no one at MS seems to want to stick their head above the ramparts...

Pete

Answer 3

np Peter. It sounds like you're on the right path then, and I'm confident you can find the solution. As for the folks at MS, there are many that are constantly sticking their heads up. But being the large company they are, they do have to be fairly careful about what they say.

I'm swamped atm, but I'll see if I can't shave out a few hours to try the example you've gotten and get back to you here with what I find. Other then putting up a worker role that will be horribly under utilized, I don't see much of an issue with what's in the article. So aside from (as you mentioned) missing SQL Azure user permissions, it should work.

Answer 4

Soon after talking to Brent, I figured out what was wrong with the ported code and I paste it below.  I am going to try to notify the author of my changes and see if I can get him to update his source.

Cheers

Pete

/*********************************************************************
 InstallSqlState.SQL												
																	
 Installs the tables, and stored procedures necessary for		  
 supporting ASP.NET session state on SQL Azure.								 

 Copyright Microsoft, Inc.
 All Rights Reserved.

 *********************************************************************/

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ASPStateTempSessions]') AND type in (N'U'))
DROP TABLE [dbo].[ASPStateTempSessions]
GO  

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ASPStateTempApplications]') AND type in (N'U'))
DROP TABLE [dbo].[ASPStateTempApplications]
GO  

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetMajorVersion') AND (type = 'P')))
	DROP PROCEDURE [dbo].GetMajorVersion
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'CreateTempTables') AND (type = 'P')))
	DROP PROCEDURE [dbo].CreateTempTables
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetVersion') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetVersion
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetHashCode') AND (type = 'P')))
	DROP PROCEDURE [dbo].GetHashCode
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetAppID') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetAppID
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetStateItem
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem2') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetStateItem2
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem3') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetStateItem3
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetStateItemExclusive
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive2') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetStateItemExclusive2
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive3') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempGetStateItemExclusive3
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempReleaseStateItemExclusive') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempReleaseStateItemExclusive
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertUninitializedItem') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempInsertUninitializedItem
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemShort') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempInsertStateItemShort
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemLong') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempInsertStateItemLong
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShort') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempUpdateStateItemShort
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShortNullLong') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempUpdateStateItemShortNullLong
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLong') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempUpdateStateItemLong
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLongNullShort') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempUpdateStateItemLongNullShort
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempRemoveStateItem') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempRemoveStateItem
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempResetTimeout') AND (type = 'P')))
	DROP PROCEDURE [dbo].TempResetTimeout
GO

IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'DeleteExpiredSessions') AND (type = 'P')))
	DROP PROCEDURE [dbo].DeleteExpiredSessions
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.GetMajorVersion
	@@ver int OUTPUT
AS
BEGIN
	DECLARE @version		nchar(100)
	DECLARE @dot			int
	DECLARE @hyphen		 int
	DECLARE @SqlToExec	 nchar(4000)

	SELECT @@ver = 7
	SELECT @version = @@Version
	SELECT @hyphen = CHARINDEX(N' - ', @version)
	IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
	BEGIN
		SELECT @hyphen = @hyphen + 3
		SELECT @dot	= CHARINDEX(N'.', @version, @hyphen)
		IF (NOT(@dot IS NULL) AND @dot > @hyphen)
		BEGIN
			SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
			SELECT @@ver	 = CONVERT(int, @version)
		END
	END
END
GO  

/*****************************************************************************/


CREATE PROCEDURE dbo.CreateTempTables
AS
	CREATE TABLE [ASPState].dbo.ASPStateTempSessions (
		SessionId		  nvarchar(88)	NOT NULL PRIMARY KEY,
		Created			 datetime		NOT NULL DEFAULT GETUTCDATE(),
		Expires			 datetime		NOT NULL,
		LockDate			datetime		NOT NULL,
		LockDateLocal	  datetime		NOT NULL,
		LockCookie		 int			 NOT NULL,
		Timeout			 int			 NOT NULL,
		Locked			 bit			 NOT NULL,
		SessionItemShort	VARBINARY(7000) NULL,
		SessionItemLong	 image		  NULL,
		Flags			  int			 NOT NULL DEFAULT 0,
	) 

	CREATE NONCLUSTERED INDEX Index_Expires ON [ASPState].dbo.ASPStateTempSessions(Expires)

	CREATE TABLE [ASPState].dbo.ASPStateTempApplications (
		AppId			  int			 NOT NULL PRIMARY KEY,
		AppName			 char(280)	  NOT NULL,
	) 

	CREATE NONCLUSTERED INDEX Index_AppName ON [ASPState].dbo.ASPStateTempApplications(AppName)

RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempGetVersion
	@ver	 char(10) OUTPUT
AS
	SELECT @ver = "2"
	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.GetHashCode
	@input varchar(280),
	@hash int OUTPUT
AS
	/* 
	  This sproc is based on this C# hash function:

		int GetHashCode(string s)
		{
			int	 hash = 5381;
			int	 len = s.Length;

			for (int i = 0; i < len; i++) {
				int	 c = Convert.ToInt32(s[i]);
				hash = ((hash << 5) + hash) ^ c;
			}

			return hash;
		}

		However, SQL 7 doesn't provide a 32-bit integer
		type that allows rollover of bits, we have to
		divide our 32bit integer into the upper and lower
		16 bits to do our calculation.
	*/
	  
	DECLARE @hi_16bit  int
	DECLARE @lo_16bit  int
	DECLARE @hi_t	  int
	DECLARE @lo_t	  int
	DECLARE @len		int
	DECLARE @i		 int
	DECLARE @c		 int
	DECLARE @carry	 int

	SET @hi_16bit = 0
	SET @lo_16bit = 5381
	
	SET @len = DATALENGTH(@input)
	SET @i = 1
	
	WHILE (@i <= @len)
	BEGIN
		SET @c = ASCII(SUBSTRING(@input, @i, 1))

		/* Formula:						
		  hash = ((hash << 5) + hash) ^ c */

		/* hash << 5 */
		SET @hi_t = @hi_16bit * 32 /* high 16bits << 5 */
		SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */
		
		SET @lo_t = @lo_16bit * 32 /* low 16bits << 5 */
		
		SET @carry = @lo_16bit & 0x1F0000 /* move low 16bits carryover to hi 16bits */
		SET @carry = @carry / 0x10000 /* >> 16 */
		SET @hi_t = @hi_t + @carry
		SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */

		/* + hash */
		SET @lo_16bit = @lo_16bit + @lo_t
		SET @hi_16bit = @hi_16bit + @hi_t + (@lo_16bit / 0x10000)
		/* delay clearing the overflow */

		/* ^c */
		SET @lo_16bit = @lo_16bit ^ @c

		/* Now clear the overflow bits */	
		SET @hi_16bit = @hi_16bit & 0xFFFF
		SET @lo_16bit = @lo_16bit & 0xFFFF

		SET @i = @i + 1
	END

	/* Do a sign extension of the hi-16bit if needed */
	IF (@hi_16bit & 0x8000 <> 0)
		SET @hi_16bit = 0xFFFF0000 | @hi_16bit

	/* Merge hi and lo 16bit back together */
	SET @hi_16bit = @hi_16bit * 0x10000 /* << 16 */
	SET @hash = @hi_16bit | @lo_16bit

	RETURN 0
GO

/*****************************************************************************/

DECLARE @cmd nchar(4000)

SET @cmd = N'
	CREATE PROCEDURE dbo.TempGetAppID
	@appName	varchar(280),
	@appId	 int OUTPUT
	AS
	SET @appName = LOWER(@appName)
	SET @appId = NULL

	SELECT @appId = AppId
	FROM [ASPState].dbo.ASPStateTempApplications
	WHERE AppName = @appName

	IF @appId IS NULL BEGIN
		BEGIN TRAN		

		SELECT @appId = AppId
		FROM [ASPState].dbo.ASPStateTempApplications WITH (TABLOCKX)
		WHERE AppName = @appName
		
		IF @appId IS NULL
		BEGIN
			EXEC GetHashCode @appName, @appId OUTPUT
			
			INSERT [ASPState].dbo.ASPStateTempApplications
			VALUES
			(@appId, @appName)
			
			IF @@ERROR = 2627 
			BEGIN
				DECLARE @dupApp varchar(280)
			
				SELECT @dupApp = RTRIM(AppName)
				FROM [ASPState].dbo.ASPStateTempApplications 
				WHERE AppId = @appId
				
				RAISERROR(''SQL session state fatal error: hash-code collision between applications ''''%s'''' and ''''%s''''. Please rename the 1st application to resolve the problem.'', 
							18, 1, @appName, @dupApp)
			END
		END

		COMMIT
	END

	RETURN 0'
EXEC(@cmd)	
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempGetStateItem
	@id		 nvarchar(88),
	@itemShort varbinary(7000) OUTPUT,
	@locked	 bit OUTPUT,
	@lockDate  datetime OUTPUT,
	@lockCookie int OUTPUT
AS
	DECLARE @length AS int
	DECLARE @now AS datetime
	SET @now = GETUTCDATE()

	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, @now), 
		@locked = Locked,
		@lockDate = LockDateLocal,
		@lockCookie = LockCookie,
		@itemShort = CASE @locked
			WHEN 0 THEN SessionItemShort
			ELSE NULL
			END,
		@length = CASE @locked
			WHEN 0 THEN DATALENGTH(SessionItemLong)
			ELSE NULL
			END
	WHERE SessionId = @id

	IF @length IS NOT NULL BEGIN
		SELECT [SessionItemLong] FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @id
	END

	RETURN 0
	
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempGetStateItem2
	@id		 nvarchar(88),
	@itemShort varbinary(7000) OUTPUT,
	@locked	 bit OUTPUT,
	@lockAge	int OUTPUT,
	@lockCookie int OUTPUT
AS
	DECLARE @length AS int
	DECLARE @now AS datetime
	SET @now = GETUTCDATE()

	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, @now), 
		@locked = Locked,
		@lockAge = DATEDIFF(second, LockDate, @now),
		@lockCookie = LockCookie,
		@itemShort = CASE @locked
			WHEN 0 THEN SessionItemShort
			ELSE NULL
			END,
		@length = CASE @locked
			WHEN 0 THEN DATALENGTH(SessionItemLong)
			ELSE NULL
			END
	WHERE SessionId = @id

	IF @length IS NOT NULL BEGIN
		SELECT [SessionItemLong] FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @id
	END

	RETURN 0
GO
			
/*****************************************************************************/

CREATE PROCEDURE dbo.TempGetStateItem3
	@id		 nvarchar(88),
	@itemShort varbinary(7000) OUTPUT,
	@locked	 bit OUTPUT,
	@lockAge	int OUTPUT,
	@lockCookie int OUTPUT,
	@actionFlags int OUTPUT
AS
	DECLARE @length AS int
	DECLARE @now AS datetime
	SET @now = GETUTCDATE()

	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, @now), 
		@locked = Locked,
		@lockAge = DATEDIFF(second, LockDate, @now),
		@lockCookie = LockCookie,
		@itemShort = CASE @locked
			WHEN 0 THEN SessionItemShort
			ELSE NULL
			END,
		@length = CASE @locked
			WHEN 0 THEN DATALENGTH(SessionItemLong)
			ELSE NULL
			END,
		/* If the Uninitialized flag (0x1) if it is set,
		  remove it and return InitializeItem (0x1) in actionFlags */
		Flags = CASE
			WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
			ELSE Flags
			END,
		@actionFlags = CASE
			WHEN (Flags & 1) <> 0 THEN 1
			ELSE 0
			END
	WHERE SessionId = @id

	IF @length IS NOT NULL BEGIN
		SELECT [SessionItemLong] FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @id
	END

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempGetStateItemExclusive
	@id		 nvarchar(88),
	@itemShort varbinary(7000) OUTPUT,
	@locked	 bit OUTPUT,
	@lockDate  datetime OUTPUT,
	@lockCookie int OUTPUT
AS
	DECLARE @length AS int
	DECLARE @now AS datetime
	DECLARE @nowLocal AS datetime

	SET @now = GETUTCDATE()
	SET @nowLocal = GETDATE()
	
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, @now), 
		LockDate = CASE Locked
			WHEN 0 THEN @now
			ELSE LockDate
			END,
		@lockDate = LockDateLocal = CASE Locked
			WHEN 0 THEN @nowLocal
			ELSE LockDateLocal
			END,
		@lockCookie = LockCookie = CASE Locked
			WHEN 0 THEN LockCookie + 1
			ELSE LockCookie
			END,
		@itemShort = CASE Locked
			WHEN 0 THEN SessionItemShort
			ELSE NULL
			END,
		@length = CASE Locked
			WHEN 0 THEN DATALENGTH(SessionItemLong)
			ELSE NULL
			END,
		@locked = Locked,
		Locked = 1
	WHERE SessionId = @id

	IF @length IS NOT NULL BEGIN
		SELECT [SessionItemLong] FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @id
	END

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempGetStateItemExclusive2
	@id		 nvarchar(88),
	@itemShort varbinary(7000) OUTPUT,
	@locked	 bit OUTPUT,
	@lockAge	int OUTPUT,
	@lockCookie int OUTPUT
AS
	DECLARE @length AS int
	DECLARE @now AS datetime
	DECLARE @nowLocal AS datetime

	SET @now = GETUTCDATE()
	SET @nowLocal = GETDATE()
	
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, @now), 
		LockDate = CASE Locked
			WHEN 0 THEN @now
			ELSE LockDate
			END,
		LockDateLocal = CASE Locked
			WHEN 0 THEN @nowLocal
			ELSE LockDateLocal
			END,
		@lockAge = CASE Locked
			WHEN 0 THEN 0
			ELSE DATEDIFF(second, LockDate, @now)
			END,
		@lockCookie = LockCookie = CASE Locked
			WHEN 0 THEN LockCookie + 1
			ELSE LockCookie
			END,
		@itemShort = CASE Locked
			WHEN 0 THEN SessionItemShort
			ELSE NULL
			END,
		@length = CASE Locked
			WHEN 0 THEN DATALENGTH(SessionItemLong)
			ELSE NULL
			END,
		@locked = Locked,
		Locked = 1
	WHERE SessionId = @id

	IF @length IS NOT NULL BEGIN
		SELECT [SessionItemLong] FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @id
	END

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempGetStateItemExclusive3
	@id		 nvarchar(88),
	@itemShort varbinary(7000) OUTPUT,
	@locked	 bit OUTPUT,
	@lockAge	int OUTPUT,
	@lockCookie int OUTPUT,
	@actionFlags int OUTPUT
AS
	DECLARE @length AS int
	DECLARE @now AS datetime
	DECLARE @nowLocal AS datetime

	SET @now = GETUTCDATE()
	SET @nowLocal = GETDATE()
	
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, @now), 
		LockDate = CASE Locked
			WHEN 0 THEN @now
			ELSE LockDate
			END,
		LockDateLocal = CASE Locked
			WHEN 0 THEN @nowLocal
			ELSE LockDateLocal
			END,
		@lockAge = CASE Locked
			WHEN 0 THEN 0
			ELSE DATEDIFF(second, LockDate, @now)
			END,
		@lockCookie = LockCookie = CASE Locked
			WHEN 0 THEN LockCookie + 1
			ELSE LockCookie
			END,
		@itemShort = CASE Locked
			WHEN 0 THEN SessionItemShort
			ELSE NULL
			END,
		@length = CASE Locked
			WHEN 0 THEN DATALENGTH(SessionItemLong)
			ELSE NULL
			END,
		@locked = Locked,
		Locked = 1,

		/* If the Uninitialized flag (0x1) if it is set,
		  remove it and return InitializeItem (0x1) in actionFlags */
		Flags = CASE
			WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
			ELSE Flags
			END,
		@actionFlags = CASE
			WHEN (Flags & 1) <> 0 THEN 1
			ELSE 0
			END
	WHERE SessionId = @id

	IF @length IS NOT NULL BEGIN
		SELECT SessionItemLong FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @id
	END

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempReleaseStateItemExclusive
	@id		 nvarchar(88),
	@lockCookie int
AS
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, GETUTCDATE()), 
		Locked = 0
	WHERE SessionId = @id AND LockCookie = @lockCookie

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempInsertUninitializedItem
	@id		 nvarchar(88),
	@itemShort varbinary(7000),
	@timeout	int
AS	

	DECLARE @now AS datetime
	DECLARE @nowLocal AS datetime
	
	SET @now = GETUTCDATE()
	SET @nowLocal = GETDATE()

	INSERT [ASPState].dbo.ASPStateTempSessions 
		(SessionId, 
		 SessionItemShort, 
		 Timeout, 
		 Expires, 
		 Locked, 
		 LockDate,
		 LockDateLocal,
		 LockCookie,
		 Flags) 
	VALUES 
		(@id, 
		 @itemShort, 
		 @timeout, 
		 DATEADD(n, @timeout, @now), 
		 0, 
		 @now,
		 @nowLocal,
		 1,
		 1)

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempInsertStateItemShort
	@id		 nvarchar(88),
	@itemShort varbinary(7000),
	@timeout	int
AS	

	DECLARE @now AS datetime
	DECLARE @nowLocal AS datetime
	
	SET @now = GETUTCDATE()
	SET @nowLocal = GETDATE()

	INSERT [ASPState].dbo.ASPStateTempSessions 
		(SessionId, 
		 SessionItemShort, 
		 Timeout, 
		 Expires, 
		 Locked, 
		 LockDate,
		 LockDateLocal,
		 LockCookie) 
	VALUES 
		(@id, 
		 @itemShort, 
		 @timeout, 
		 DATEADD(n, @timeout, @now), 
		 0, 
		 @now,
		 @nowLocal,
		 1)

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempInsertStateItemLong
	@id		 nvarchar(88),
	@itemLong  image,
	@timeout	int
AS	
	DECLARE @now AS datetime
	DECLARE @nowLocal AS datetime
	
	SET @now = GETUTCDATE()
	SET @nowLocal = GETDATE()

	INSERT [ASPState].dbo.ASPStateTempSessions 
		(SessionId, 
		 SessionItemLong, 
		 Timeout, 
		 Expires, 
		 Locked, 
		 LockDate,
		 LockDateLocal,
		 LockCookie) 
	VALUES 
		(@id, 
		 @itemLong, 
		 @timeout, 
		 DATEADD(n, @timeout, @now), 
		 0, 
		 @now,
		 @nowLocal,
		 1)

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempUpdateStateItemShort
	@id		 nvarchar(88),
	@itemShort varbinary(7000),
	@timeout	int,
	@lockCookie int
AS	
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 
		SessionItemShort = @itemShort, 
		Timeout = @timeout,
		Locked = 0
	WHERE SessionId = @id AND LockCookie = @lockCookie

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempUpdateStateItemShortNullLong
	@id		 nvarchar(88),
	@itemShort varbinary(7000),
	@timeout	int,
	@lockCookie int
AS	
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 
		SessionItemShort = @itemShort, 
		SessionItemLong = NULL, 
		Timeout = @timeout,
		Locked = 0
	WHERE SessionId = @id AND LockCookie = @lockCookie

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempUpdateStateItemLong
	@id		 nvarchar(88),
	@itemLong  image,
	@timeout	int,
	@lockCookie int
AS	
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 
		SessionItemLong = @itemLong,
		Timeout = @timeout,
		Locked = 0
	WHERE SessionId = @id AND LockCookie = @lockCookie

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempUpdateStateItemLongNullShort
	@id		 nvarchar(88),
	@itemLong  image,
	@timeout	int,
	@lockCookie int
AS	
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 
		SessionItemLong = @itemLong, 
		SessionItemShort = NULL,
		Timeout = @timeout,
		Locked = 0
	WHERE SessionId = @id AND LockCookie = @lockCookie

	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempRemoveStateItem
	@id	 nvarchar(88),
	@lockCookie int
AS
	DELETE [ASPState].dbo.ASPStateTempSessions
	WHERE SessionId = @id AND LockCookie = @lockCookie
	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.TempResetTimeout
	@id	 nvarchar(88)
AS
	UPDATE [ASPState].dbo.ASPStateTempSessions
	SET Expires = DATEADD(n, Timeout, GETUTCDATE())
	WHERE SessionId = @id
	RETURN 0
GO

/*****************************************************************************/

CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
	DECLARE @now datetime
	SET @now = GETUTCDATE()

	DELETE [ASPState].dbo.ASPStateTempSessions
	WHERE Expires < @now

	RETURN 0
GO 
			
/*****************************************************************************/

EXECUTE dbo.CreateTempTables
GO

/*************************************************************/
/*************************************************************/
/*************************************************************/
/*************************************************************/

PRINT ''
PRINT '------------------------------------------'
PRINT 'Completed execution of InstallSqlState.SQL'
PRINT '------------------------------------------'

IF NOT EXISTS(SELECT NAME FROM sys.sysusers where name='ASPState')
BEGIN
	CREATE USER [ASPState] FOR LOGIN [ASPState]
END
GO
EXEC sp_addrolemember N'db_datawriter', N'ASPState'
GO
EXEC sp_addrolemember N'db_datareader', N'ASPState'
GO
EXEC sp_addrolemember N'db_owner', N'ASPState'
GO

Answer 5

Peter,

Can you tell me more precisely what you changed to make it work?  I will make the changes to the blog post.

Thanks,

Wayne Berry

Answer 6

In many of the original SPs, there was the following snippet which you had removed:

        @textptr = CASE @locked
          WHEN 0 THEN TEXTPTR(SessionItemLong)
          ELSE NULL
          END,
        @length = CASE @locked
          WHEN 0 THEN DATALENGTH(SessionItemLong)
          ELSE NULL
          END
...
...

      IF @length IS NOT NULL BEGIN
        READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
      END

Which you had removed cos TEXTPTR and READTEXT are not available within SQL Azure.

This removal was causing the provider to fail with the cast exception, as the provider was expecting to see a resultset in certain cases

I re-added an Azure compatible implementation in all the SPs that were previously using it.  Like you, I also did NOT carry forward the @textPtr assignment - but I did carry forward the @length assignment.  I replaced the READTEXT with:

	IF @length IS NOT NULL BEGIN
		SELECT [SessionItemLong] FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @id
	END

I'm hoping that is the correct compatible implementation

 

Pete

 

 

Answer 7

Peter,

I'm now using your script and it seems to be working well in our limited scenario. I just wanted to point out that I did find that you can further restrict the ASPState users permissions to simply EXECUTE permissions. So at the very end of your script you can replace the 3 EXEC sp_addrolemember with this one line:

GRANT EXECUTE TO [ASPState]

Thanks for putting the effort in to making this work. I have to say I am surprised Microsoft doesn't have a blessed version of this for use in SQL Azure. Doesn't almost any web site need this? Perhaps everyone else is using Azure Storage. For us SQL Azure was the obvious choice because we already have a SQL Azure database, so we just added these new tables and sprocs to our existing database for essentially no cost.

I am eagerly awaiting the official release of Azure AppFabric Caching to replace this with a much more performant solution. For now I think this will be workable.

Answer 8

Hi, I use that code for my session state provider but it seems that the timeout is not working.

Suppose to timeout within N (set in web.config) minute when there's no interaction on any pages, but it just never time out.

Wondering if the problem persist on your end as well.

---

regards, wely

Answer 9

Sir, I could hug you.

After 2 years on Sql Azure SessionState, out of nowhere one of my sites started to behave badly with the cast exception flagged in this post.

Your updates to the SP's fixed the issue.

Answer 10

Hi

I have run this sql query on Azure database and get these errors,would you be able to help me please?I also added the sql to create a new database ASPState

Messages
Msg 40515, Level 15, State 1, Procedure CreateTempTables, Line 16 [Batch Start Line 168]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempGetAppID, Line 16 [Batch Start Line 295]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempApplications' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempGetStateItem, Line 16 [Batch Start Line 346]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempGetStateItem2, Line 16 [Batch Start Line 382]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempGetStateItem3, Line 16 [Batch Start Line 417]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempGetStateItemExclusive, Line 16 [Batch Start Line 463]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempGetStateItemExclusive2, Line 16 [Batch Start Line 512]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempGetStateItemExclusive3, Line 16 [Batch Start Line 565]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempReleaseStateItemExclusive, Line 16 [Batch Start Line 630]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempInsertUninitializedItem, Line 16 [Batch Start Line 644]
Reference to database and/or server name in 'ASPState.dbo.ASPStateTempSessions' is not supported in this version of SQL Server.
Msg 40515, Level 15, State 1, Procedure TempInsertStateItemShort, Line 16 [Batch Start Line 682]