text: /* First uninstall - this section is exactly the same as uninstall.sql */
  USE master
  GO
  
  /* Drop the database containing our sprocs */
  IF DB_ID('ASPState') IS NOT NULL BEGIN
      DROP DATABASE ASPState
  END
  GO
  
  /* Drop temporary tables */
  IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempSessions' AND type = 'U') BEGIN
      DROP TABLE tempdb..ASPStateTempSessions
  END
  GO
  
  IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempApplications' AND type = 'U') BEGIN
      DROP TABLE tempdb..ASPStateTempApplications
  END
  GO
  
  /* Drop the startup procedure */
  DECLARE @PROCID int
  SET @PROCID = OBJECT_ID('ASPState_Startup') 
  IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN
      DROP PROCEDURE ASPState_Startup 
  END
  GO
  
  /* Drop the obsolete startup enabler */
  DECLARE @PROCID int
  SET @PROCID = OBJECT_ID('EnableASPStateStartup') 
  IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN
      DROP PROCEDURE EnableASPStateStartup
  END
  GO
  
  /* Drop the obsolete startup disabler */
  DECLARE @PROCID int
  SET @PROCID = OBJECT_ID('DisableASPStateStartup') 
  IF @PROCID IS NOT NULL AND OBJECTPROPERTY(@PROCID, 'IsProcedure') = 1 BEGIN
      DROP PROCEDURE DisableASPStateStartup
  END
  GO
  
  /* Drop the ASPState_DeleteExpiredSessions_Job */
  DECLARE @JobID BINARY(16)  
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'ASPState_Job_DeleteExpiredSessions')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
      -- Check if the job is a multi-server job  
      IF (EXISTS (SELECT  * 
                FROM    msdb.dbo.sysjobservers 
                WHERE   (job_id = @JobID) AND (server_id <> 0))) 
      BEGIN 
          -- There is, so abort the script 
          RAISERROR (N'Unable to import job ''ASPState_Job_DeleteExpiredSessions'' since there is already a multi-server job with this name.', 16, 1) 
      END 
      ELSE 
          -- Delete the [local] job 
          EXECUTE msdb.dbo.sp_delete_job @job_name = N'ASPState_Job_DeleteExpiredSessions' 
  END
  
  USE master
  GO
  
  /* Create and populate the ASPState database */
  CREATE DATABASE ASPState
  GO
  
  USE ASPstate
  GO
  
  SET QUOTED_IDENTIFIER OFF 
  GO
  SET ANSI_NULLS ON 
  GO
  
  CREATE PROCEDURE DropTempTables
  AS
      IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempSessions' AND type = 'U') BEGIN
          DROP TABLE tempdb..ASPStateTempSessions
      END
  
      IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'ASPStateTempApplications' AND type = 'U') BEGIN
          DROP TABLE tempdb..ASPStateTempApplications
      END
  
      RETURN 0
  GO
      
  CREATE PROCEDURE CreateTempTables
  AS
      /*
       * Note that we cannot create user-defined data types in
       * tempdb because sp_addtype must be run in the context
       * of the current database, and we cannot switch to 
       * tempdb from a stored procedure.
       */
  
      CREATE TABLE tempdb..ASPStateTempSessions (
          SessionId           CHAR(32)        NOT NULL PRIMARY KEY,
          Created             DATETIME        NOT NULL DEFAULT GETDATE(),
          Expires             DATETIME        NOT NULL,
          LockDate            DATETIME        NOT NULL,
          LockCookie          INT             NOT NULL,
          Timeout             INT             NOT NULL,
          Locked              BIT             NOT NULL,
          SessionItemShort    VARBINARY(7000) NULL,
          SessionItemLong     IMAGE           NULL,
      ) 
  
      CREATE TABLE tempdb..ASPStateTempApplications (
          AppId               INT             NOT NULL IDENTITY PRIMARY KEY,
          AppName             CHAR(280)       NOT NULL,
      ) 
  
      CREATE NONCLUSTERED INDEX Index_AppName ON tempdb..ASPStateTempApplications(AppName)
  
      RETURN 0
  GO      
  
  CREATE PROCEDURE ResetData
  AS
      EXECUTE DropTempTables
      EXECUTE CreateTempTables
      RETURN 0
  GO
     
  EXECUTE sp_addtype tSessionId, 'CHAR(32)',  'NOT NULL'
  GO
  
  EXECUTE sp_addtype tAppName, 'VARCHAR(280)', 'NOT NULL'
  GO
  
  EXECUTE sp_addtype tSessionItemShort, 'VARBINARY(7000)'
  GO
  
  EXECUTE sp_addtype tSessionItemLong, 'IMAGE'
  GO
  
  EXECUTE sp_addtype tTextPtr, 'VARBINARY(16)'
  GO
  
  CREATE PROCEDURE TempGetAppId
      @appName    tAppName,
      @appId      INT OUTPUT
  AS
      SELECT @appId = AppId
      FROM tempdb..ASPStateTempApplications
      WHERE AppName = @appName
  
      IF @appId IS NULL BEGIN
          INSERT tempdb..ASPStateTempApplications
              (AppName)
          VALUES
              (@appName)
  
          SELECT @appId = AppId
          FROM tempdb..ASPStateTempApplications
          WHERE AppName = @appName
      END
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempGetStateItem
      @id         tSessionId,
      @itemShort  tSessionItemShort OUTPUT,
      @locked     BIT OUTPUT,
      @lockDate   DATETIME OUTPUT,
      @lockCookie INT OUTPUT
  AS
      DECLARE @textptr AS tTextPtr
      DECLARE @length AS INT
      DECLARE @now as DATETIME
      SET @now = GETDATE()
  
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, @now), 
          @locked = Locked,
          @lockDate = LockDate,
          @lockCookie = LockCookie,
          @itemShort = CASE @locked
              WHEN 0 THEN SessionItemShort
              ELSE NULL
              END,
          @textptr = CASE @locked
              WHEN 0 THEN TEXTPTR(SessionItemLong)
              ELSE NULL
              END,
          @length = CASE @locked
              WHEN 0 THEN DATALENGTH(SessionItemLong)
              ELSE NULL
              END
      WHERE SessionId = @id
      IF @length IS NOT NULL BEGIN
          READTEXT tempdb..ASPStateTempSessions.SessionItemLong @textptr 0 @length
      END
  
      RETURN 0
  GO
  
  
  CREATE PROCEDURE TempGetStateItemExclusive
      @id         tSessionId,
      @itemShort  tSessionItemShort OUTPUT,
      @locked     BIT OUTPUT,
      @lockDate   DATETIME OUTPUT,
      @lockCookie INT OUTPUT
  AS
      DECLARE @textptr AS tTextPtr
      DECLARE @length AS INT
      DECLARE @now as DATETIME
  
      SET @now = GETDATE()
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, @now), 
          @lockDate = LockDate = CASE Locked
              WHEN 0 THEN @now
              ELSE LockDate
              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 TEXTPTR(SessionItemLong)
              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
          READTEXT tempdb..ASPStateTempSessions.SessionItemLong @textptr 0 @length
      END
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempReleaseStateItemExclusive
      @id         tSessionId,
      @lockCookie INT
  AS
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, GETDATE()), 
          Locked = 0
      WHERE SessionId = @id AND LockCookie = @lockCookie
  
      RETURN 0
  GO
  
  
  CREATE PROCEDURE TempInsertStateItemShort
      @id         tSessionId,
      @itemShort  tSessionItemShort,
      @timeout    INT
  AS    
  
      DECLARE @now as DATETIME
      SET @now = GETDATE()
  
      INSERT tempdb..ASPStateTempSessions 
          (SessionId, 
           SessionItemShort, 
           Timeout, 
           Expires, 
           Locked, 
           LockDate,
           LockCookie) 
      VALUES 
          (@id, 
           @itemShort, 
           @timeout, 
           DATEADD(n, @timeout, @now), 
           0, 
           @now,
           1)
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempInsertStateItemLong
      @id         tSessionId,
      @itemLong   tSessionItemLong,
      @timeout    INT
  AS    
      DECLARE @now as DATETIME
      SET @now = GETDATE()
  
      INSERT tempdb..ASPStateTempSessions 
          (SessionId, 
           SessionItemLong, 
           Timeout, 
           Expires, 
           Locked, 
           LockDate,
           LockCookie) 
      VALUES 
          (@id, 
           @itemLong, 
           @timeout, 
           DATEADD(n, @timeout, @now), 
           0, 
           @now,
           1)
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempUpdateStateItemShort
      @id         tSessionId,
      @itemShort  tSessionItemShort,
      @timeout    INT,
      @lockCookie INT
  AS    
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, GETDATE()), 
          SessionItemShort = @itemShort, 
          Timeout = @timeout,
          Locked = 0
      WHERE SessionId = @id AND LockCookie = @lockCookie
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempUpdateStateItemShortNullLong
      @id         tSessionId,
      @itemShort  tSessionItemShort,
      @timeout    INT,
      @lockCookie INT
  AS    
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, GETDATE()), 
          SessionItemShort = @itemShort, 
          SessionItemLong = NULL, 
          Timeout = @timeout,
          Locked = 0
      WHERE SessionId = @id AND LockCookie = @lockCookie
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempUpdateStateItemLong
      @id         tSessionId,
      @itemLong   tSessionItemLong,
      @timeout    INT,
      @lockCookie INT
  AS    
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, GETDATE()), 
          SessionItemLong = @itemLong,
          Timeout = @timeout,
          Locked = 0
      WHERE SessionId = @id AND LockCookie = @lockCookie
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempUpdateStateItemLongNullShort
      @id         tSessionId,
      @itemLong   tSessionItemLong,
      @timeout    INT,
      @lockCookie INT
  AS    
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, GETDATE()), 
          SessionItemLong = @itemLong, 
          SessionItemShort = NULL,
          Timeout = @timeout,
          Locked = 0
      WHERE SessionId = @id AND LockCookie = @lockCookie
  
      RETURN 0
  GO
  
  CREATE PROCEDURE TempRemoveStateItem
      @id     tSessionId,
      @lockCookie INT
  AS
      DELETE tempdb..ASPStateTempSessions
      WHERE SessionId = @id AND LockCookie = @lockCookie
      RETURN 0
  GO
              
  CREATE PROCEDURE TempResetTimeout
      @id     tSessionId
  AS
      UPDATE tempdb..ASPStateTempSessions
      SET Expires = DATEADD(n, Timeout, GETDATE())
      WHERE SessionId = @id
      RETURN 0
  GO
              
  CREATE PROCEDURE DeleteExpiredSessions
  AS
      DECLARE @now DATETIME
      SET @now = GETDATE()
  
      DELETE tempdb..ASPStateTempSessions
      WHERE Expires < @now
  
      RETURN 0
  GO
              
  EXECUTE CreateTempTables
  GO
  
  /* Create the startup procedure */
  USE master
  GO
  
  CREATE PROCEDURE ASPState_Startup 
  AS
      EXECUTE ASPState..CreateTempTables
  
      RETURN 0
  GO      
  
  EXECUTE sp_procoption @procname='ASPState_Startup', @optionname='startup', @optionvalue='true'
  
  /* Create the job to delete expired sessions */
  BEGIN TRANSACTION            
      DECLARE @JobID BINARY(16)  
      DECLARE @ReturnCode INT    
      SELECT @ReturnCode = 0     
  
      -- Add job category
      IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
          EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
  
      -- Add the job
      EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
              @job_id = @JobID OUTPUT, 
              @job_name = N'ASPState_Job_DeleteExpiredSessions', 
              @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
      EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
              @job_id = @JobID,
              @step_id = 1, 
              @step_name = N'ASPState_JobStep_DeleteExpiredSessions', 
              @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
      EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
              @job_id = @JobID, 
              @name = N'ASPState_JobSchedule_DeleteExpiredSessions', 
              @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
  
  