1/**//*********************************************************************
   2  InstallSqlState.SQL                                                
   3                                                                    
   4  Installs the tables, and stored procedures necessary for           
   5  supporting ASP.NET session state.                                  
   6
   7  Copyright Microsoft, Inc.
   8  All Rights Reserved.
   9
  10 *********************************************************************/
  11
  12SET QUOTED_IDENTIFIER OFF 
  13GO
  14SET ANSI_NULLS ON 
  15GO
  16
  17PRINT ''
  18PRINT '-----------------------------------------'
  19PRINT 'Starting execution of InstallSqlState.SQL'
  20PRINT '-----------------------------------------'
  21PRINT '--------------------------------------------------'
  22PRINT 'Note:                                             '
  23PRINT 'Do not run this file manually.                    '
  24PRINT 'You should use aspnet_regsql.exe to install       '
  25PRINT 'and uninstall SQL session state.                  '
  26PRINT ''
  27PRINT 'Run ''aspnet_regsql.exe -?'' for details.         '
  28PRINT '--------------------------------------------------'
  29GO
  30
  31/**//*****************************************************************************/
  32
  33USE master
  34GO
  35
  36/**//* Create and populate the session state database */
  37
  38IF DB_ID(N'ASPState') IS NULL BEGIN
  39    DECLARE @cmd nvarchar(500)
  40    SET @cmd = N'CREATE DATABASE [ASPState]'
  41    EXEC(@cmd)
  42END    
  43GO
  44
  45/**//* Drop all tables, startup procedures, stored procedures and types. */
  46
  47/**//* Drop the DeleteExpiredSessions_Job */
  48
  49DECLARE @jobname nvarchar(200)
  50SET @jobname = N'ASPState' + '_Job_DeleteExpiredSessions' 
  51
  52-- Delete the [local] job 
  53-- We expected to get an error if the job doesn't exist.
  54PRINT 'If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.'
  55
  56EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname
  57GO
  58
  59DECLARE @sstype nvarchar(128)
  60SET @sstype = N'sstype_temp'
  61
  62IF UPPER(@sstype) = 'SSTYPE_TEMP' AND OBJECT_ID(N'dbo.ASPState_Startup', 'P') IS NOT NULL BEGIN
  63    DROP PROCEDURE dbo.ASPState_Startup
  64END    
  65
  66USE [tempdb]
  67GO
  68
  69IF OBJECT_ID(N'dbo.ASPStateTempSessions','U') IS NOT NULL BEGIN
  70    DROP TABLE dbo.ASPStateTempSessions
  71END
  72
  73IF OBJECT_ID(N'dbo.ASPStateTempApplications','U') IS NOT NULL BEGIN
  74    DROP TABLE dbo.ASPStateTempApplications
  75END
  76
  77USE [ASPState]
  78GO
  79
  80IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetMajorVersion') AND (type = 'P')))
  81    DROP PROCEDURE [dbo].GetMajorVersion
  82GO
  83
  84IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'CreateTempTables') AND (type = 'P')))
  85    DROP PROCEDURE [dbo].CreateTempTables
  86GO
  87
  88IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetVersion') AND (type = 'P')))
  89    DROP PROCEDURE [dbo].TempGetVersion
  90GO
  91
  92IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetHashCode') AND (type = 'P')))
  93    DROP PROCEDURE [dbo].GetHashCode
  94GO
  95
  96IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetAppID') AND (type = 'P')))
  97    DROP PROCEDURE [dbo].TempGetAppID
  98GO
  99
 100IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem') AND (type = 'P')))
 101    DROP PROCEDURE [dbo].TempGetStateItem
 102GO
 103
 104IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem2') AND (type = 'P')))
 105    DROP PROCEDURE [dbo].TempGetStateItem2
 106GO
 107
 108IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem3') AND (type = 'P')))
 109    DROP PROCEDURE [dbo].TempGetStateItem3
 110GO
 111
 112IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive') AND (type = 'P')))
 113    DROP PROCEDURE [dbo].TempGetStateItemExclusive
 114GO
 115
 116IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive2') AND (type = 'P')))
 117    DROP PROCEDURE [dbo].TempGetStateItemExclusive2
 118GO
 119
 120IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive3') AND (type = 'P')))
 121    DROP PROCEDURE [dbo].TempGetStateItemExclusive3
 122GO
 123
 124IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempReleaseStateItemExclusive') AND (type = 'P')))
 125    DROP PROCEDURE [dbo].TempReleaseStateItemExclusive
 126GO
 127
 128IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertUninitializedItem') AND (type = 'P')))
 129    DROP PROCEDURE [dbo].TempInsertUninitializedItem
 130GO
 131
 132IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemShort') AND (type = 'P')))
 133    DROP PROCEDURE [dbo].TempInsertStateItemShort
 134GO
 135
 136IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemLong') AND (type = 'P')))
 137    DROP PROCEDURE [dbo].TempInsertStateItemLong
 138GO
 139
 140IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShort') AND (type = 'P')))
 141    DROP PROCEDURE [dbo].TempUpdateStateItemShort
 142GO
 143
 144IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShortNullLong') AND (type = 'P')))
 145    DROP PROCEDURE [dbo].TempUpdateStateItemShortNullLong
 146GO
 147
 148IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLong') AND (type = 'P')))
 149    DROP PROCEDURE [dbo].TempUpdateStateItemLong
 150GO
 151
 152IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLongNullShort') AND (type = 'P')))
 153    DROP PROCEDURE [dbo].TempUpdateStateItemLongNullShort
 154GO
 155
 156IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempRemoveStateItem') AND (type = 'P')))
 157    DROP PROCEDURE [dbo].TempRemoveStateItem
 158GO
 159
 160IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempResetTimeout') AND (type = 'P')))
 161    DROP PROCEDURE [dbo].TempResetTimeout
 162GO
 163
 164IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'DeleteExpiredSessions') AND (type = 'P')))
 165    DROP PROCEDURE [dbo].DeleteExpiredSessions
 166GO
 167
 168IF EXISTS(SELECT name FROM systypes WHERE name ='tSessionId')
 169    EXECUTE sp_droptype tSessionId
 170GO
 171
 172IF EXISTS(SELECT name FROM systypes WHERE name ='tAppName')
 173    EXECUTE sp_droptype tAppName
 174GO
 175
 176IF EXISTS(SELECT name FROM systypes WHERE name ='tSessionItemShort')
 177    EXECUTE sp_droptype tSessionItemShort
 178GO
 179
 180IF EXISTS(SELECT name FROM systypes WHERE name ='tSessionItemLong')
 181    EXECUTE sp_droptype tSessionItemLong
 182GO
 183
 184IF EXISTS(SELECT name FROM systypes WHERE name ='tTextPtr')
 185    EXECUTE sp_droptype tTextPtr
 186GO
 187
 188/**//*****************************************************************************/
 189
 190CREATE PROCEDURE dbo.GetMajorVersion
 191    @@ver int OUTPUT
 192AS
 193BEGIN
 194    DECLARE @version        nchar(100)
 195    DECLARE @dot            int
 196    DECLARE @hyphen         int
 197    DECLARE @SqlToExec      nchar(4000)
 198
 199    SELECT @@ver = 7
 200    SELECT @version = @@Version
 201    SELECT @hyphen  = CHARINDEX(N' - ', @version)
 202    IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
 203    BEGIN
 204        SELECT @hyphen = @hyphen + 3
 205        SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
 206        IF (NOT(@dot IS NULL) AND @dot > @hyphen)
 207        BEGIN
 208            SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
 209            SELECT @@ver     = CONVERT(int, @version)
 210        END
 211    END
 212END
 213GO   
 214
 215/**//*****************************************************************************/
 216
 217USE [ASPState]
 218
 219/**//* Find out the version */
 220DECLARE @ver int
 221EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
 222
 223DECLARE @cmd nchar(4000)
 224
 225IF (@ver >= 8)
 226    SET @cmd = N'
 227        CREATE PROCEDURE dbo.CreateTempTables
 228        AS
 229            CREATE TABLE [tempdb].dbo.ASPStateTempSessions (
 230                SessionId           nvarchar(88)    NOT NULL PRIMARY KEY,
 231                Created             datetime        NOT NULL DEFAULT GETUTCDATE(),
 232                Expires             datetime        NOT NULL,
 233                LockDate            datetime        NOT NULL,
 234                LockDateLocal       datetime        NOT NULL,
 235                LockCookie          int             NOT NULL,
 236                Timeout             int             NOT NULL,
 237                Locked              bit             NOT NULL,
 238                SessionItemShort    VARBINARY(7000) NULL,
 239                SessionItemLong     image           NULL,
 240                Flags               int             NOT NULL DEFAULT 0,
 241            ) 
 242
 243            CREATE NONCLUSTERED INDEX Index_Expires ON [tempdb].dbo.ASPStateTempSessions(Expires)
 244
 245            CREATE TABLE [tempdb].dbo.ASPStateTempApplications (
 246                AppId               int             NOT NULL PRIMARY KEY,
 247                AppName             char(280)       NOT NULL,
 248            ) 
 249
 250            CREATE NONCLUSTERED INDEX Index_AppName ON [tempdb].dbo.ASPStateTempApplications(AppName)
 251
 252            RETURN 0'
 253ELSE
 254    SET @cmd = N'
 255        CREATE PROCEDURE dbo.CreateTempTables
 256        AS
 257            CREATE TABLE [tempdb].dbo.ASPStateTempSessions (
 258                SessionId           nvarchar(88)    NOT NULL PRIMARY KEY,
 259                Created             datetime        NOT NULL DEFAULT GETDATE(),
 260                Expires             datetime        NOT NULL,
 261                LockDate            datetime        NOT NULL,
 262                LockCookie          int             NOT NULL,
 263                Timeout             int             NOT NULL,
 264                Locked              bit             NOT NULL,
 265                SessionItemShort    VARBINARY(7000) NULL,
 266                SessionItemLong     image           NULL,
 267                Flags               int             NOT NULL DEFAULT 0,
 268            ) 
 269
 270            CREATE NONCLUSTERED INDEX Index_Expires ON [tempdb].dbo.ASPStateTempSessions(Expires)
 271
 272            CREATE TABLE [tempdb].dbo.ASPStateTempApplications (
 273                AppId               int             NOT NULL PRIMARY KEY,
 274                AppName             char(280)       NOT NULL,
 275            ) 
 276
 277            CREATE NONCLUSTERED INDEX Index_AppName ON [tempdb].dbo.ASPStateTempApplications(AppName)
 278
 279            RETURN 0'
 280
 281EXEC (@cmd)
 282GO   
 283
 284/**//*****************************************************************************/
 285
 286EXECUTE sp_addtype tSessionId, 'nvarchar(88)',  'NOT NULL'
 287GO
 288
 289EXECUTE sp_addtype tAppName, 'varchar(280)', 'NOT NULL'
 290GO
 291
 292EXECUTE sp_addtype tSessionItemShort, 'varbinary(7000)'
 293GO
 294
 295EXECUTE sp_addtype tSessionItemLong, 'image'
 296GO
 297
 298EXECUTE sp_addtype tTextPtr, 'varbinary(16)'
 299GO
 300
 301/**//*****************************************************************************/
 302
 303CREATE PROCEDURE dbo.TempGetVersion
 304    @ver      char(10) OUTPUT
 305AS
 306    SELECT @ver = "2"
 307    RETURN 0
 308GO
 309
 310/**//*****************************************************************************/
 311
 312CREATE PROCEDURE dbo.GetHashCode
 313    @input tAppName,
 314    @hash int OUTPUT
 315AS
 316    /**//* 
 317       This sproc is based on this C# hash function:
 318
 319        int GetHashCode(string s)
 320        {
 321            int     hash = 5381;
 322            int