Answered by:
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.
- Edited by Peter McEvoy Monday, August 23, 2010 3:31 PM missing URL
- Moved by Brian AurichMicrosoft employee Wednesday, September 29, 2010 6:53 AM migration (From:SQL Azure - Archive)
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 -
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 -
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 -
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, welyWednesday, 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 -
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]Thursday, October 10, 2019 2:24 PM