Monday 31 August 2009

Silverlight: Offline talk at Devlink

I just want to say a big thanks to everyone who came to my session at Devlink.  I had an absolutely fantastic time and I really enjoyed presenting this session to you guys.

I really felt that this session was more than just a presentation but also a conversation and I thank you for participating in it with me.

I really hope to return to Devlink next year (and other U.S. Conferences) so I hope to see you again.  I will post up my Devlink experiences later on this week hopefully.

Again, a big thank you for coming to my talk and the slides and demo code are available from here.

Sunday 30 August 2009

SQL Azure Database Session Database

Ok, so here is the good news and the bad news.

The good news is that I created my SQL Azure Session Database Script (from hacking InstallPersistSQLState.SQL) you can’t use InstallSQLState.sql as it uses tempdb.

The bad news is that to make it work you need to create a custom session provider (no easy task).  You need a custom provider as the SQL Session Provider needs to use the @@ parameter in the GetMajorVersion Stored Procedure which SQL Azure database doesn’t support.  I also haven’t tested this script, so use at your own risk

I am realizing I don’t have time to write the provider at the moment as I am still in the middle of writing my “Azure in Action” book (along with Brian Prince), so here is the script for the database.

All you need to do is Create a Database in SQL Azure called ASPState and run this script.

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

  Copyright Microsoft, Inc.
  All Rights Reserved.
  Modified by chrishayuk to work in SQL Azure Database
  P.S. You will need a worker role or console session to clean up expired sessions

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

--SET QUOTED_IDENTIFIER OFF
--GO
--SET ANSI_NULLS ON
--GO

PRINT ''
PRINT '------------------------------------------------'
PRINT 'Starting execution of InstallPersistSqlState.SQL'
PRINT '------------------------------------------------'
PRINT ''
PRINT '--------------------------------------------------'
PRINT 'Note:                                             '
PRINT 'This file is included for backward compatibility  '
PRINT 'only.  You should use aspnet_regsql.exe to install'
PRINT 'and uninstall SQL session state.                  '
PRINT ''
PRINT 'Run ''aspnet_regsql.exe -?'' for details.         '
PRINT '--------------------------------------------------'
GO

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

-- chrishayuk -- Commented out the creation of the database
--/* Create and populate the session state database */
--IF DB_ID(N'ASPState') IS NULL BEGIN
--    DECLARE @cmd nvarchar(500)
--    SET @cmd = N'CREATE DATABASE [ASPState]'
--    EXEC(@cmd)
--END   
--GO

/* Drop all tables, startup procedures, stored procedures and types. */

/* Drop the DeleteExpiredSessions_Job */

-- chrishayuk -- Commented out the dropping of the DeleteExpiredSessions Job
--DECLARE @jobname nvarchar(200)
--SET @jobname = N'ASPState' + '_Job_DeleteExpiredSessions'
-- Delete the [local] job
-- We expected to get an error if the job doesn't exist.
--PRINT 'If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.'
--EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname
--GO

DECLARE @sstype nvarchar(128)
SET @sstype = N'sstype_persisted'

IF UPPER(@sstype) = 'SSTYPE_TEMP' AND OBJECT_ID(N'dbo.ASPState_Startup', 'P') IS NOT NULL BEGIN
    DROP PROCEDURE dbo.ASPState_Startup
END   

IF OBJECT_ID(N'dbo.ASPStateTempSessions','U') IS NOT NULL BEGIN
    DROP TABLE dbo.ASPStateTempSessions
END

IF OBJECT_ID(N'dbo.ASPStateTempApplications','U') IS NOT NULL BEGIN
    DROP TABLE dbo.ASPStateTempApplications
END

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE PROCEDURE dbo.GetMajorVersion
    @ver int OUTPUT
AS
BEGIN
    SET @ver = 10
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     varbinary(max)  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.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

/*****************************************************************************/
    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  
GO

/*****************************************************************************/
CREATE PROCEDURE dbo.TempGetStateItem
    @id         nvarchar(88),
    @itemShort  varbinary(7000) OUTPUT,
    @locked     bit OUTPUT,
    @lockDate   datetime OUTPUT,
    @lockCookie int OUTPUT
AS
    DECLARE @textptr AS varbinary(max)
    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,
        @textptr = CASE @locked
            WHEN 0 THEN SessionItemLong
            ELSE NULL
            END,
        @length = CASE @locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END
    WHERE SessionId = @id

    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 @textptr AS varbinary(max)
    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,
        @textptr = CASE @locked
            WHEN 0 THEN SessionItemLong
            ELSE NULL
            END,
        @length = CASE @locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END
    WHERE SessionId = @id

    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 @textptr AS varbinary(max)
    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,
        @textptr = CASE @locked
            WHEN 0 THEN SessionItemLong
            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

    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 @textptr AS varbinary(max)
    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,
        @textptr = CASE Locked
            WHEN 0 THEN SessionItemLong
            ELSE NULL
            END,
        @length = CASE Locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END,
        @locked = Locked,
        Locked = 1
    WHERE SessionId = @id

    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 @textptr AS varbinary(max)
    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,
        @textptr = CASE Locked
            WHEN 0 THEN SessionItemLong
            ELSE NULL
            END,
        @length = CASE Locked
            WHEN 0 THEN DATALENGTH(SessionItemLong)
            ELSE NULL
            END,
        @locked = Locked,
        Locked = 1
    WHERE SessionId = @id

    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 @textptr AS varbinary(max)
    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,
        @textptr = CASE Locked
            WHEN 0 THEN SessionItemLong
            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

    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   varbinary(max),
    @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   varbinary(max),
        @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   varbinary(max),
    @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

DECLARE @sstype nvarchar(128)
SET @sstype = N'sstype_persisted'

IF UPPER(@sstype) = 'SSTYPE_TEMP' BEGIN
    DECLARE @cmd nchar(4000)

    SET @cmd = N'
        /* Create the startup procedure */
        CREATE PROCEDURE dbo.ASPState_Startup
        AS
            EXECUTE ASPState.dbo.CreateTempTables

            RETURN 0'
    EXEC(@cmd)
    EXECUTE sp_procoption @ProcName='dbo.ASPState_Startup', @OptionName='startup', @OptionValue='true'
END   

-- chrishayuk -- Commented out the creation of the DeleteExpiredSessions Job
--/*****************************************************************************/

--/* Create the job to delete expired sessions */

---- Add job category
---- We expect an error if the category already exists.
--PRINT 'If the category already exists, an error from msdb.dbo.sp_add_category is expected.'
--EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
--GO

--BEGIN TRANSACTION           
--    DECLARE @JobID BINARY(16) 
--    DECLARE @ReturnCode int   
--    DECLARE @nameT nchar(200)
--    SELECT @ReturnCode = 0    

--    -- Add the job
--    SET @nameT = N'ASPState' + '_Job_DeleteExpiredSessions'
--    EXECUTE @ReturnCode = msdb.dbo.sp_add_job
--            @job_id = @JobID OUTPUT,
--            @job_name = @nameT,
--            @owner_login_name = NULL,
--            @description = N'Deletes expired sessions from the session state database.',
--            @category_name = N'[Uncategorized (Local)]',
--            @enabled = 1,
--            @notify_level_email = 0,
--            @notify_level_page = 0,
--            @notify_level_netsend = 0,
--            @notify_level_eventlog = 0,
--            @delete_level= 0

--    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--    -- Add the job steps
--    SET @nameT = N'ASPState' + '_JobStep_DeleteExpiredSessions'
--    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
--            @job_id = @JobID,
--            @step_id = 1,
--            @step_name = @nameT,
--            @command = N'EXECUTE DeleteExpiredSessions',
--            @database_name = N'ASPState',
--            @server = N'',
--            @database_user_name = N'',
--            @subsystem = N'TSQL',
--            @cmdexec_success_code = 0,
--            @flags = 0,
--            @retry_attempts = 0,
--            @retry_interval = 1,
--            @output_file_name = N'',
--            @on_success_step_id = 0,
--            @on_success_action = 1,
--            @on_fail_step_id = 0,
--            @on_fail_action = 2

--    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--    EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
--    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--    -- Add the job schedules
--    SET @nameT = N'ASPState' + '_JobSchedule_DeleteExpiredSessions'
--    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
--            @job_id = @JobID,
--            @name = @nameT,
--            @enabled = 1,
--            @freq_type = 4,    
--            @active_start_date = 20001016,
--            @active_start_time = 0,
--            @freq_interval = 1,
--            @freq_subday_type = 4,
--            @freq_subday_interval = 1,
--            @freq_relative_interval = 0,
--            @freq_recurrence_factor = 0,
--            @active_end_date = 99991231,
--            @active_end_time = 235959

--    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--    -- Add the Target Servers
--    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
--    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--    COMMIT TRANSACTION         
--    GOTO   EndSave             
--QuitWithRollback:
--    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
--EndSave:
--GO

--/*************************************************************/
--/*************************************************************/
--/*************************************************************/
--/*************************************************************/

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

Monday 24 August 2009

Using SQL Azure Database in SQL Management Studio

OK, so I’ve figured it now. You can use SQL Azure Database in SQL Azure Management Studio, you just can’t use object explorer.

So this means you will be able to run queries but not be able to view stored proc lists, tables etc in the Object Explorer window. If you need to do that then you can always use Universal SQL Editor as described in my previous article

So the simplest way of doing this is to

  • When connected to your existing database
    • Click New Query button
    • Within Query Window
      • Right Click and Select Change Connection
        • Enter the server name (including ctp.database.windows.net e.g. servername.ctp.database.windows.net)
        • Select SQL Server Authentication
        • Enter Username (including servername, excluding ctp.database.windows.net e.g chris@servername
        • Enter Password
        • Click Options
          • In Connection Properties Tab
            • Change Connect to Database to database name (e.g. HawaiianShirts), just type it

This will allow you to run queries in SQL Management Studio but not use object explorer

Alternatively you can login as normal but disable object explorer

Tools->Options->(Environment->General)->At Startup = "Open New Query Window" instead of "Open Object Explorer"

Sunday 23 August 2009

Connecting to SQL Azure Database with a management tool

Whilst working on my new book Azure in Action (first 3 chapters are available now online), I thought I’d write up how to connect to SQL Azure Database using a database browser tool.

If you are lucky enough to have received your SQL Azure Database invitation token you will have noticed that you cannot connect SQL Server Management Studio to your cloud database.

However this is not to say that you cannot use another tool to perform the same job.

Universal SQL Editor

The Tool I have used is Universal SQL Editor which has a 14 day trial and is a reasonable cost at $39 beyond that.  It’s a pretty good tool actually (please note I’m not affiliated with them in any way), it has intellisense and can talk to loads of different db types.

Setting up a DSN Connection

In the SQL Azure Portal I already created a database called “HawaiianShirts” which I will now connect to using the tool.  To do that I created a file DSN which points to my DB.  I called the DSN file “HawaiianShirts.dsn” and the contents of the DSN are below:

[ODBC]
DRIVER=SQL Server Native Client 10.0
UID=<myusername>@<myservername>
WSID=<mypcname>
APP=2007 Microsoft Office system
SERVER=<myservername>.ctp.database.windows.net
DATABASE=HawaiianShirts

So for example (not real data)

[ODBC]
DRIVER=SQL Server Native Client 10.0
UID=chris@a123456
WSID=CHRIS-PC
APP=2007 Microsoft Office system
SERVER=a123456.ctp.database.windows.net
DATABASE=HawaiianShirts

I can now use this file DSN to open a connection to the database.  I can use this editor to both create tables, stored procedures and view/edit data.

Here is a screenshot of me using the tool to edit some data in a table in SQL Azure Database

image