解决asp.net丢失session的方法文件

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

转载于:https://www.cnblogs.com/xxjudfc/p/3752727.html