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

有时候,我们在某一重要的时间段需要监控某张表的变化情况,包含插入、更新、删除。举例来说,当我们把数据导出到外部的系统时,我们希望导出的是全部的数据,而且最好是导出上次导出之后变动的数据。
作为DBA,我们可采传统的触发器操作,来构建一个元数据表或一个时间戳列来监控数据的变化。
代码如下:Code Listing 1 该代码在 SQL 2005(SP3), SQL 2008 R2 (RTM with
cu5)测试通过 复制代码 代码如下:
——————- –Method 1: TRIGGER ——————- –Base Table
Definition IF OBJECT_ID(‘CheckSumTest’, ‘U’) IS NOT NULL DROP TABLE
CheckSumTest GO CREATE TABLE CheckSumTest ( id int IDENTITY(1,1) NOT
NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2 varchar(1) NOT NULL ) GO
INSERT dbo.CheckSumTest (vc1, vc2) SELECT ‘a’, ‘b’ INSERT
dbo.CheckSumTest (vc1, vc2) SELECT ‘b’, ‘a’ GO –Create Audit Summary
Table to hold Meta-Data IF OBJECT_ID(‘dbo.TableAuditSummary’, ‘U’) IS
NOT NULL DROP TABLE dbo.TableAuditSummary CREATE TABLE
dbo.TableAuditSummary ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TableName sysname NOT NULL, LastUpdate DATETIME NOT NULL, LastExport
DATETIME NOT NULL ) GO INSERT dbo.TableAuditSummary (TableName,
LastUpdate, LastExport) VALUES (‘dbo.CheckSumTest’, GETDATE(),
GETDATE()) GO –Tables that need exporting SELECT * FROM
dbo.TableAuditSummary WHERE LastUpdateLastExport –Create Trigger on all
Base Tables –This fires on any insert/update/delete and writes new
LastUpdate column for the table set to Current Date and Time IF
OBJECT_ID(‘dbo.trg_CheckSumTest_MaintainAuditSummary’, ‘TR’) IS NOT
NULL DROP TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary GO CREATE
TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary ON dbo.CheckSumTest
AFTER INSERT, UPDATE, DELETE AS BEGIN IF (object_id(‘dbo.CheckSumTest’)
IS NOT NULL) UPDATE dbo.TableAuditSummary SET LastUpdate=GETDATE() WHERE
TableName=’dbo.CheckSumTest’ END GO –Make an Update UPDATE
dbo.CheckSumTest SET vc1=’b’, vc2=’a’ WHERE id=1 UPDATE dbo.CheckSumTest
SET vc1=’a’, vc2=’b’ WHERE id=2 –Check Meta-Data SELECT * FROM
dbo.TableAuditSummary WHERE LastUpdateLastExport –When we have Exported
the data, we run the following to reset MetaData UPDATE
dbo.TableAuditSummary SET LastExport=GETDATE() WHERE
LastUpdateLastExport 最近我正在读关天SQLSERVER在线帮助相关的知识,
我接触到了 SQL Server CHECKSUM(), BINARY_CHECKSUM(), and
CHECKSUM_AGG() 这几个函数,
由此突然想到这些函数是不是也可以监控表的数据变化,而事实证明CHECKSUM_AGG()
函数尽管被描述为检测表的变化,但这里不适用. 使用 CheckSum() and
CheckSum_Agg() 函数 CHECKSUM_AGG() 函数, 在Books OnLine
和许多相关的站点上是这样描述的, 通常用于检测一个表的数据是否更改.
这是一个代替触发器的更好的方法,只是该操作会引起表扫描的操作。于是我这次我仍然使用元数据来跟踪数据的变化,只是新建了列LastChkSum代替了LastUpdate,该列用于保存CHECKSUM_AGG(BINARY_CHECKSUM(*)),它将会在全表中产生一个唯一值,以区别数据的变化情况。
代码如下: Listing 2. 复制代码 代码如下:
——————————————— –Method 2 : using
CheckSum (not reliable) ———————————————
–Base Table Definition IF OBJECT_ID(‘CheckSumTest’, ‘U’) IS NOT NULL
DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int
IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2
varchar(1) NOT NULL ) GO INSERT dbo.CheckSumTest (vc1, vc2) SELECT ‘a’,
‘b’ INSERT dbo.CheckSumTest (vc1, vc2) SELECT ‘b’, ‘a’ GO –Create Audit
Summary Table to hold Meta-Data IF OBJECT_ID(‘dbo.TableAuditSummary’,
‘U’) IS NOT NULL DROP TABLE dbo.TableAuditSummary CREATE TABLE
dbo.TableAuditSummary ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TableName sysname NOT NULL, LastChkSum INT NOT NULL ) GO INSERT
dbo.TableAuditSummary (TableName, LastChkSum) SELECT ‘dbo.CheckSumTest’,
CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest GO –Tables
that need exporting SELECT * FROM dbo.TableAuditSummary WHERE
TableName=’dbo.CheckSumTest’ AND LastChkSum(SELECT
CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) UNION ALL …
–Make a Simple (Single row) Update UPDATE dbo.CheckSumTest SET vc1=’c’,
vc2=’a’ WHERE id=1 –Tables that need exporting SELECT * FROM
dbo.TableAuditSummary WHERE TableName=’dbo.CheckSumTest’ AND
LastChkSum(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM
dbo.CheckSumTest) UNION ALL … –Reset MetaData UPDATE
dbo.TableAuditSummary SET LastChkSum=(SELECT
CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) WHERE
TableName=’dbo.CheckSumTest’ –Make a Symmetric change UPDATE
dbo.CheckSumTest SET vc1=’b’, vc2=’a’ WHERE id=1 UPDATE dbo.CheckSumTest
SET vc1=’c’, vc2=’a’ WHERE id=2 –Tables that need exporting (no rows
returned as CHECKSUM_AGG() has not changed!!) SELECT * FROM
dbo.TableAuditSummary WHERE TableName=’dbo.CheckSumTest’ AND
LastChkSum(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM
dbo.CheckSumTest) UNION ALLCode Listing 2
正如你所看到的那样,对于单个的变化的情况,CHECKSUM是使用比较好的,但是CHECKSUM_AGG()却不能反应数据的变化
代码如下:Code Listing 3 复制代码
代码如下: –Base Table Definition IF OBJECT_ID(‘CheckSumTest’, ‘U’) IS
NOT NULL DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int
IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2
varchar(1) NOT NULL, chksum1 AS (CHECKSUM(id, vc1, vc2)), chksum2 AS
(BINARY_CHECKSUM(id, vc1, vc2)) ) GO INSERT dbo.CheckSumTest (vc1, vc2)
SELECT ‘a’, ‘b’ INSERT dbo.CheckSumTest (vc1, vc2) SELECT ‘b’, ‘a’ GO
–Show Computed Columns and CheckSum_Agg() value = 199555 SELECT *
FROM CheckSumTest SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM
CheckSumTest –Make a Simple (Single row) Update UPDATE dbo.CheckSumTest
SET vc1=’c’, vc2=’a’ WHERE id=1 –Show Computed Columns and
CheckSum_Agg() value = 204816 (Ok) SELECT * FROM CheckSumTest SELECT
CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest –Make a Symmetric
change UPDATE dbo.CheckSumTest SET vc1=’b’, vc2=’a’ WHERE id=1 UPDATE
dbo.CheckSumTest SET vc1=’c’, vc2=’a’ WHERE id=2 –Show Computed Columns
and CheckSum_Agg() value = 204816 (Not Ok!) SELECT * FROM CheckSumTest
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest
我们会发现调整前后 CHECKSUM_AGG(BINARY_CHECKSUM(*))
的值是一样的,不能区分 结论: CHECKSUM_AGG()
函数尽管被描述为能监测表数据的变化,在实际测试中是不行的。尤其是对表进行对称数据修改时,无法监测
作者:Tyler Ning

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图