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