none
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.

    Monday, August 23, 2010 3:21 PM

Answers

  • 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
    
    

    • Marked as answer by Peter McEvoy Tuesday, August 24, 2010 1:30 PM
    Tuesday, August 24, 2010 1:25 PM

All replies

  • 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.

    Tuesday, August 24, 2010 11:01 AM
    Moderator
  • 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

    Tuesday, August 24, 2010 11:38 AM
  • 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.

    Tuesday, August 24, 2010 11:49 AM
    Moderator
  • 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
    
    

    • Marked as answer by Peter McEvoy Tuesday, August 24, 2010 1:30 PM
    Tuesday, August 24, 2010 1:25 PM
  • 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

    Tuesday, August 24, 2010 3:10 PM
    Moderator
  • 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

     

     

    Tuesday, August 24, 2010 3:41 PM
  • 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.

    Tuesday, January 18, 2011 6:23 PM
  • 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
    Wednesday, June 15, 2011 9:32 AM
  • 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.

    Thursday, November 28, 2013 8:35 PM