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
12 SET QUOTED_IDENTIFIER OFF
13 GO
14 SET ANSI_NULLS ON
15 GO
16
17 PRINT ''
18 PRINT '-----------------------------------------'
19 PRINT 'Starting execution of InstallSqlState.SQL'
20 PRINT '-----------------------------------------'
21 PRINT '--------------------------------------------------'
22 PRINT 'Note: '
23 PRINT 'Do not run this file manually. '
24 PRINT 'You should use aspnet_regsql.exe to install '
25 PRINT 'and uninstall SQL session state. '
26 PRINT ''
27 PRINT 'Run ''aspnet_regsql.exe -?'' for details. '
28 PRINT '--------------------------------------------------'
29 GO
30
31 /*****************************************************************************/
32
33 USE master
34 GO
35
36 /* Create and populate the session state database */
37
38 IF DB_ID(N'ASPState') IS NULL BEGIN
39 DECLARE @cmd nvarchar(500)
40 SET @cmd = N'CREATE DATABASE [ASPState]'
41 EXEC(@cmd)
42 END
43 GO
44
45 /* Drop all tables, startup procedures, stored procedures and types. */
46
47 /* Drop the DeleteExpiredSessions_Job */
48
49 DECLARE @jobname nvarchar(200)
50 SET @jobname = N'ASPState' + '_Job_DeleteExpiredSessions'
51
52 -- Delete the [local] job
53 -- We expected to get an error if the job doesn't exist.
54 PRINT 'If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.'
55
56 EXECUTE msdb.dbo.sp_delete_job @job_name = @jobname
57 GO
58
59 DECLARE @sstype nvarchar(128)
60 SET @sstype = N'sstype_temp'
61
62 IF UPPER(@sstype) = 'SSTYPE_TEMP' AND OBJECT_ID(N'dbo.ASPState_Startup', 'P') IS NOT NULL BEGIN
63 DROP PROCEDURE dbo.ASPState_Startup
64 END
65
66 USE [tempdb]
67 GO
68
69 IF OBJECT_ID(N'dbo.ASPStateTempSessions','U') IS NOT NULL BEGIN
70 DROP TABLE dbo.ASPStateTempSessions
71 END
72
73 IF OBJECT_ID(N'dbo.ASPStateTempApplications','U') IS NOT NULL BEGIN
74 DROP TABLE dbo.ASPStateTempApplications
75 END
76
77 USE [ASPState]
78 GO
79
80 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetMajorVersion') AND (type = 'P')))
81 DROP PROCEDURE [dbo].GetMajorVersion
82 GO
83
84 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'CreateTempTables') AND (type = 'P')))
85 DROP PROCEDURE [dbo].CreateTempTables
86 GO
87
88 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetVersion') AND (type = 'P')))
89 DROP PROCEDURE [dbo].TempGetVersion
90 GO
91
92 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'GetHashCode') AND (type = 'P')))
93 DROP PROCEDURE [dbo].GetHashCode
94 GO
95
96 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetAppID') AND (type = 'P')))
97 DROP PROCEDURE [dbo].TempGetAppID
98 GO
99
100 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem') AND (type = 'P')))
101 DROP PROCEDURE [dbo].TempGetStateItem
102 GO
103
104 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem2') AND (type = 'P')))
105 DROP PROCEDURE [dbo].TempGetStateItem2
106 GO
107
108 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItem3') AND (type = 'P')))
109 DROP PROCEDURE [dbo].TempGetStateItem3
110 GO
111
112 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive') AND (type = 'P')))
113 DROP PROCEDURE [dbo].TempGetStateItemExclusive
114 GO
115
116 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive2') AND (type = 'P')))
117 DROP PROCEDURE [dbo].TempGetStateItemExclusive2
118 GO
119
120 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempGetStateItemExclusive3') AND (type = 'P')))
121 DROP PROCEDURE [dbo].TempGetStateItemExclusive3
122 GO
123
124 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempReleaseStateItemExclusive') AND (type = 'P')))
125 DROP PROCEDURE [dbo].TempReleaseStateItemExclusive
126 GO
127
128 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertUninitializedItem') AND (type = 'P')))
129 DROP PROCEDURE [dbo].TempInsertUninitializedItem
130 GO
131
132 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemShort') AND (type = 'P')))
133 DROP PROCEDURE [dbo].TempInsertStateItemShort
134 GO
135
136 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempInsertStateItemLong') AND (type = 'P')))
137 DROP PROCEDURE [dbo].TempInsertStateItemLong
138 GO
139
140 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShort') AND (type = 'P')))
141 DROP PROCEDURE [dbo].TempUpdateStateItemShort
142 GO
143
144 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemShortNullLong') AND (type = 'P')))
145 DROP PROCEDURE [dbo].TempUpdateStateItemShortNullLong
146 GO
147
148 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLong') AND (type = 'P')))
149 DROP PROCEDURE [dbo].TempUpdateStateItemLong
150 GO
151
152 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempUpdateStateItemLongNullShort') AND (type = 'P')))
153 DROP PROCEDURE [dbo].TempUpdateStateItemLongNullShort
154 GO
155
156 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempRemoveStateItem') AND (type = 'P')))
157 DROP PROCEDURE [dbo].TempRemoveStateItem
158 GO
159
160 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'TempResetTimeout') AND (type = 'P')))
161 DROP PROCEDURE [dbo].TempResetTimeout
162 GO
163
164 IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'DeleteExpiredSessions') AND (type = 'P')))
165 DROP PROCEDURE [dbo].DeleteExpiredSessions
166 GO
167
168 IF EXISTS(SELECT name FROM systypes WHERE name ='tSessionId')
169 EXECUTE sp_droptype tSessionId
170 GO
171
172 IF EXISTS(SELECT name FROM systypes WHERE name ='tAppName')
173 EXECUTE sp_droptype tAppName
174 GO
175
176 IF EXISTS(SELECT name FROM systypes WHERE name ='tSessionItemShort')
177 EXECUTE sp_droptype tSessionItemShort
178 GO
179
180 IF EXISTS(SELECT name FROM systypes WHERE name ='tSessionItemLong')
181 EXECUTE sp_droptype tSessionItemLong
182 GO
183
184 IF EXISTS(SELECT name FROM systypes WHERE name ='tTextPtr')
185 EXECUTE sp_droptype tTextPtr
186 GO
187
188 /*****************************************************************************/
189
190 CREATE PROCEDURE dbo.GetMajorVersion
191 @@ver int OUTPUT
192 AS
193 BEGIN
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
212 END
213 GO
214
215 /*****************************************************************************/
216
217 USE [ASPState]
218
219 /* Find out the version */
220 DECLARE @ver int
221 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
222
223 DECLARE @cmd nchar(4000)
224
225 IF (@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'
253 ELSE
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
281 EXEC (@cmd)
282 GO
283
284 /*****************************************************************************/
285
286 EXECUTE sp_addtype tSessionId, 'nvarchar(88)', 'NOT NULL'
287 GO
288
289 EXECUTE sp_addtype tAppName, 'varchar(280)', 'NOT NULL'
290 GO
291
292 EXECUTE sp_addtype tSessionItemShort, 'varbinary(7000)'
293 GO
294
295 EXECUTE sp_addtype tSessionItemLong, 'image'
296 GO
297
298 EXECUTE sp_addtype tTextPtr, 'varbinary(16)'
299 GO
300
301 /*****************************************************************************/
302
303 CREATE PROCEDURE dbo.TempGetVersion
304 @ver char(10) OUTPUT
305 AS
306 SELECT @ver = "2"
307 RETURN 0
308 GO
309
310 /*****************************************************************************/
311
312 CREATE PROCEDURE dbo.GetHashCode
313 @input tAppName,
314 @hash int OUTPUT
315 AS
316 /*
317 This sproc is based on this C# hash function:
318
319 int GetHashCode(string s)
320 {
321 int hash = 5381;
322 int len = s.Length;
323
324 for (int i = 0; i < len; i++) {
325 int c = Convert.ToInt32(s[i]);
326 hash = ((hash << 5) + hash) ^ c;
327 }
328
329 return hash;
330 }
331
332 However, SQL 7 doesn't provide a 32-bit integer
333 type that allows rollover of bits, we have to
334 divide our 32bit integer into the upper and lower
335 16 bits to do our calculation.
336 */
337
338 DECLARE @hi_16bit int
339 DECLARE @lo_16bit int
340 DECLARE @hi_t int
341 DECLARE @lo_t int
342 DECLARE @len int
343 DECLARE @i int
344 DECLARE @c int
345 DECLARE @carry int
346
347 SET @hi_16bit = 0
348 SET @lo_16bit = 5381
349
350 SET @len = DATALENGTH(@input)
351 SET @i = 1
352
353 WHILE (@i <= @len)
354 BEGIN
355 SET @c = ASCII(SUBSTRING(@input, @i, 1))
356
357 /* Formula:
358 hash = ((hash << 5) + hash) ^ c */
359
360 /* hash << 5 */
361 SET @hi_t = @hi_16bit * 32 /* high 16bits << 5 */
362 SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */
363
364 SET @lo_t = @lo_16bit * 32 /* low 16bits << 5 */
365
366 SET @carry = @lo_16bit & 0x1F0000 /* move low 16bits carryover to hi 16bits */
367 SET @carry = @carry / 0x10000 /* >> 16 */
368 SET @hi_t = @hi_t + @carry
369 SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */
370
371 /* + hash */
372 SET @lo_16bit = @lo_16bit + @lo_t
373 SET @hi_16bit = @hi_16bit + @hi_t + (@lo_16bit / 0x10000)
374 /* delay clearing the overflow */
375
376 /* ^c */
377 SET @lo_16bit = @lo_16bit ^ @c
378
379 /* Now clear the overflow bits */
380 SET @hi_16bit = @hi_16bit & 0xFFFF
381 SET @lo_16bit = @lo_16bit & 0xFFFF
382
383 SET @i = @i + 1
384 END
385
386 /* Do a sign extension of the hi-16bit if needed */
387 IF (@hi_16bit & 0x8000 <> 0)
388 SET @hi_16bit = 0xFFFF0000 | @hi_16bit
389
390 /* Merge hi and lo 16bit back together */
391 SET @hi_16bit = @hi_16bit * 0x10000 /* << 16 */
392 SET @hash = @hi_16bit | @lo_16bit
393
394 RETURN 0
395 GO
396
397 /*****************************************************************************/
398
399 DECLARE @cmd nchar(4000)
400
401 SET @cmd = N'
402 CREATE PROCEDURE dbo.TempGetAppID
403 @appName tAppName,
404 @appId int OUTPUT
405 AS
406 SET @appName = LOWER(@appName)
407 SET @appId = NULL
408
409 SELECT @appId = AppId
410 FROM [tempdb].dbo.ASPStateTempApplications
411 WHERE AppName = @appName
412
413 IF @appId IS NULL BEGIN
414 BEGIN TRAN
415
416 SELECT @appId = AppId
417 FROM [tempdb].dbo.ASPStateTempApplications WITH (TABLOCKX)
418 WHERE AppName = @appName
419
420 IF @appId IS NULL
421 BEGIN
422 EXEC GetHashCode @appName, @appId OUTPUT
423
424 INSERT [tempdb].dbo.ASPStateTempApplications
425 VALUES
426 (@appId, @appName)
427
428 IF @@ERROR = 2627
429 BEGIN
430 DECLARE @dupApp tAppName
431
432 SELECT @dupApp = RTRIM(AppName)
433 FROM [tempdb].dbo.ASPStateTempApplications
434 WHERE AppId = @appId
435
436 RAISERROR(''SQL session state fatal error: hash-code collision between applications ''''%s'''' and ''''%s''''. Please rename the 1st application to resolve the problem.'',
437 18, 1, @appName, @dupApp)
438 END
439 END
440
441 COMMIT
442 END
443
444 RETURN 0'
445 EXEC(@cmd)
446 GO
447
448 /*****************************************************************************/
449
450 /* Find out the version */
451
452 DECLARE @ver int
453 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
454 DECLARE @cmd nchar(4000)
455 IF (@ver >= 8)
456 SET @cmd = N'
457 CREATE PROCEDURE dbo.TempGetStateItem
458 @id tSessionId,
459 @itemShort tSessionItemShort OUTPUT,
460 @locked bit OUTPUT,
461 @lockDate datetime OUTPUT,
462 @lockCookie int OUTPUT
463 AS
464 DECLARE @textptr AS tTextPtr
465 DECLARE @length AS int
466 DECLARE @now AS datetime
467 SET @now = GETUTCDATE()
468
469 UPDATE [tempdb].dbo.ASPStateTempSessions
470 SET Expires = DATEADD(n, Timeout, @now),
471 @locked = Locked,
472 @lockDate = LockDateLocal,
473 @lockCookie = LockCookie,
474 @itemShort = CASE @locked
475 WHEN 0 THEN SessionItemShort
476 ELSE NULL
477 END,
478 @textptr = CASE @locked
479 WHEN 0 THEN TEXTPTR(SessionItemLong)
480 ELSE NULL
481 END,
482 @length = CASE @locked
483 WHEN 0 THEN DATALENGTH(SessionItemLong)
484 ELSE NULL
485 END
486 WHERE SessionId = @id
487 IF @length IS NOT NULL BEGIN
488 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
489 END
490
491 RETURN 0'
492 ELSE
493 SET @cmd = N'
494 CREATE PROCEDURE dbo.TempGetStateItem
495 @id tSessionId,
496 @itemShort tSessionItemShort OUTPUT,
497 @locked bit OUTPUT,
498 @lockDate datetime OUTPUT,
499 @lockCookie int OUTPUT
500 AS
501 DECLARE @textptr AS tTextPtr
502 DECLARE @length AS int
503 DECLARE @now AS datetime
504 SET @now = GETDATE()
505
506 UPDATE [tempdb].dbo.ASPStateTempSessions
507 SET Expires = DATEADD(n, Timeout, @now),
508 @locked = Locked,
509 @lockDate = LockDate,
510 @lockCookie = LockCookie,
511 @itemShort = CASE @locked
512 WHEN 0 THEN SessionItemShort
513 ELSE NULL
514 END,
515 @textptr = CASE @locked
516 WHEN 0 THEN TEXTPTR(SessionItemLong)
517 ELSE NULL
518 END,
519 @length = CASE @locked
520 WHEN 0 THEN DATALENGTH(SessionItemLong)
521 ELSE NULL
522 END
523 WHERE SessionId = @id
524 IF @length IS NOT NULL BEGIN
525 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
526 END
527
528 RETURN 0'
529
530 EXEC (@cmd)
531 GO
532
533 /*****************************************************************************/
534
535 DECLARE @ver int
536 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
537 DECLARE @cmd nchar(4000)
538 IF (@ver >= 8)
539 SET @cmd = N'
540 CREATE PROCEDURE dbo.TempGetStateItem2
541 @id tSessionId,
542 @itemShort tSessionItemShort OUTPUT,
543 @locked bit OUTPUT,
544 @lockAge int OUTPUT,
545 @lockCookie int OUTPUT
546 AS
547 DECLARE @textptr AS tTextPtr
548 DECLARE @length AS int
549 DECLARE @now AS datetime
550 SET @now = GETUTCDATE()
551
552 UPDATE [tempdb].dbo.ASPStateTempSessions
553 SET Expires = DATEADD(n, Timeout, @now),
554 @locked = Locked,
555 @lockAge = DATEDIFF(second, LockDate, @now),
556 @lockCookie = LockCookie,
557 @itemShort = CASE @locked
558 WHEN 0 THEN SessionItemShort
559 ELSE NULL
560 END,
561 @textptr = CASE @locked
562 WHEN 0 THEN TEXTPTR(SessionItemLong)
563 ELSE NULL
564 END,
565 @length = CASE @locked
566 WHEN 0 THEN DATALENGTH(SessionItemLong)
567 ELSE NULL
568 END
569 WHERE SessionId = @id
570 IF @length IS NOT NULL BEGIN
571 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
572 END
573
574 RETURN 0'
575
576 EXEC (@cmd)
577 GO
578
579
580 /*****************************************************************************/
581
582 /* Find out the version */
583
584 DECLARE @ver int
585 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
586 DECLARE @cmd nchar(4000)
587 IF (@ver >= 8)
588 SET @cmd = N'
589 CREATE PROCEDURE dbo.TempGetStateItem3
590 @id tSessionId,
591 @itemShort tSessionItemShort OUTPUT,
592 @locked bit OUTPUT,
593 @lockAge int OUTPUT,
594 @lockCookie int OUTPUT,
595 @actionFlags int OUTPUT
596 AS
597 DECLARE @textptr AS tTextPtr
598 DECLARE @length AS int
599 DECLARE @now AS datetime
600 SET @now = GETUTCDATE()
601
602 UPDATE [tempdb].dbo.ASPStateTempSessions
603 SET Expires = DATEADD(n, Timeout, @now),
604 @locked = Locked,
605 @lockAge = DATEDIFF(second, LockDate, @now),
606 @lockCookie = LockCookie,
607 @itemShort = CASE @locked
608 WHEN 0 THEN SessionItemShort
609 ELSE NULL
610 END,
611 @textptr = CASE @locked
612 WHEN 0 THEN TEXTPTR(SessionItemLong)
613 ELSE NULL
614 END,
615 @length = CASE @locked
616 WHEN 0 THEN DATALENGTH(SessionItemLong)
617 ELSE NULL
618 END,
619
620 /* If the Uninitialized flag (0x1) if it is set,
621 remove it and return InitializeItem (0x1) in actionFlags */
622 Flags = CASE
623 WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
624 ELSE Flags
625 END,
626 @actionFlags = CASE
627 WHEN (Flags & 1) <> 0 THEN 1
628 ELSE 0
629 END
630 WHERE SessionId = @id
631 IF @length IS NOT NULL BEGIN
632 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
633 END
634
635 RETURN 0'
636 ELSE
637 SET @cmd = N'
638 CREATE PROCEDURE dbo.TempGetStateItem3
639 @id tSessionId,
640 @itemShort tSessionItemShort OUTPUT,
641 @locked bit OUTPUT,
642 @lockDate datetime OUTPUT,
643 @lockCookie int OUTPUT,
644 @actionFlags int OUTPUT
645 AS
646 DECLARE @textptr AS tTextPtr
647 DECLARE @length AS int
648 DECLARE @now AS datetime
649 SET @now = GETDATE()
650
651 UPDATE [tempdb].dbo.ASPStateTempSessions
652 SET Expires = DATEADD(n, Timeout, @now),
653 @locked = Locked,
654 @lockDate = LockDate,
655 @lockCookie = LockCookie,
656 @itemShort = CASE @locked
657 WHEN 0 THEN SessionItemShort
658 ELSE NULL
659 END,
660 @textptr = CASE @locked
661 WHEN 0 THEN TEXTPTR(SessionItemLong)
662 ELSE NULL
663 END,
664 @length = CASE @locked
665 WHEN 0 THEN DATALENGTH(SessionItemLong)
666 ELSE NULL
667 END,
668
669 /* If the Uninitialized flag (0x1) if it is set,
670 remove it and return InitializeItem (0x1) in actionFlags */
671 Flags = CASE
672 WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
673 ELSE Flags
674 END,
675 @actionFlags = CASE
676 WHEN (Flags & 1) <> 0 THEN 1
677 ELSE 0
678 END
679 WHERE SessionId = @id
680 IF @length IS NOT NULL BEGIN
681 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
682 END
683
684 RETURN 0'
685
686 EXEC (@cmd)
687 GO
688
689 /*****************************************************************************/
690
691 DECLARE @ver int
692 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
693 DECLARE @cmd nchar(4000)
694 IF (@ver >= 8)
695 SET @cmd = N'
696 CREATE PROCEDURE dbo.TempGetStateItemExclusive
697 @id tSessionId,
698 @itemShort tSessionItemShort OUTPUT,
699 @locked bit OUTPUT,
700 @lockDate datetime OUTPUT,
701 @lockCookie int OUTPUT
702 AS
703 DECLARE @textptr AS tTextPtr
704 DECLARE @length AS int
705 DECLARE @now AS datetime
706 DECLARE @nowLocal AS datetime
707
708 SET @now = GETUTCDATE()
709 SET @nowLocal = GETDATE()
710
711 UPDATE [tempdb].dbo.ASPStateTempSessions
712 SET Expires = DATEADD(n, Timeout, @now),
713 LockDate = CASE Locked
714 WHEN 0 THEN @now
715 ELSE LockDate
716 END,
717 @lockDate = LockDateLocal = CASE Locked
718 WHEN 0 THEN @nowLocal
719 ELSE LockDateLocal
720 END,
721 @lockCookie = LockCookie = CASE Locked
722 WHEN 0 THEN LockCookie + 1
723 ELSE LockCookie
724 END,
725 @itemShort = CASE Locked
726 WHEN 0 THEN SessionItemShort
727 ELSE NULL
728 END,
729 @textptr = CASE Locked
730 WHEN 0 THEN TEXTPTR(SessionItemLong)
731 ELSE NULL
732 END,
733 @length = CASE Locked
734 WHEN 0 THEN DATALENGTH(SessionItemLong)
735 ELSE NULL
736 END,
737 @locked = Locked,
738 Locked = 1
739 WHERE SessionId = @id
740 IF @length IS NOT NULL BEGIN
741 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
742 END
743
744 RETURN 0'
745 ELSE
746 SET @cmd = N'
747 CREATE PROCEDURE dbo.TempGetStateItemExclusive
748 @id tSessionId,
749 @itemShort tSessionItemShort OUTPUT,
750 @locked bit OUTPUT,
751 @lockDate datetime OUTPUT,
752 @lockCookie int OUTPUT
753 AS
754 DECLARE @textptr AS tTextPtr
755 DECLARE @length AS int
756 DECLARE @now AS datetime
757
758 SET @now = GETDATE()
759 UPDATE [tempdb].dbo.ASPStateTempSessions
760 SET Expires = DATEADD(n, Timeout, @now),
761 @lockDate = LockDate = CASE Locked
762 WHEN 0 THEN @now
763 ELSE LockDate
764 END,
765 @lockCookie = LockCookie = CASE Locked
766 WHEN 0 THEN LockCookie + 1
767 ELSE LockCookie
768 END,
769 @itemShort = CASE Locked
770 WHEN 0 THEN SessionItemShort
771 ELSE NULL
772 END,
773 @textptr = CASE Locked
774 WHEN 0 THEN TEXTPTR(SessionItemLong)
775 ELSE NULL
776 END,
777 @length = CASE Locked
778 WHEN 0 THEN DATALENGTH(SessionItemLong)
779 ELSE NULL
780 END,
781 @locked = Locked,
782 Locked = 1
783 WHERE SessionId = @id
784 IF @length IS NOT NULL BEGIN
785 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
786 END
787
788 RETURN 0'
789
790 EXEC (@cmd)
791 GO
792
793
794 /*****************************************************************************/
795
796 DECLARE @ver int
797 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
798 DECLARE @cmd nchar(4000)
799 IF (@ver >= 8)
800 SET @cmd = N'
801 CREATE PROCEDURE dbo.TempGetStateItemExclusive2
802 @id tSessionId,
803 @itemShort tSessionItemShort OUTPUT,
804 @locked bit OUTPUT,
805 @lockAge int OUTPUT,
806 @lockCookie int OUTPUT
807 AS
808 DECLARE @textptr AS tTextPtr
809 DECLARE @length AS int
810 DECLARE @now AS datetime
811 DECLARE @nowLocal AS datetime
812
813 SET @now = GETUTCDATE()
814 SET @nowLocal = GETDATE()
815
816 UPDATE [tempdb].dbo.ASPStateTempSessions
817 SET Expires = DATEADD(n, Timeout, @now),
818 LockDate = CASE Locked
819 WHEN 0 THEN @now
820 ELSE LockDate
821 END,
822 LockDateLocal = CASE Locked
823 WHEN 0 THEN @nowLocal
824 ELSE LockDateLocal
825 END,
826 @lockAge = CASE Locked
827 WHEN 0 THEN 0
828 ELSE DATEDIFF(second, LockDate, @now)
829 END,
830 @lockCookie = LockCookie = CASE Locked
831 WHEN 0 THEN LockCookie + 1
832 ELSE LockCookie
833 END,
834 @itemShort = CASE Locked
835 WHEN 0 THEN SessionItemShort
836 ELSE NULL
837 END,
838 @textptr = CASE Locked
839 WHEN 0 THEN TEXTPTR(SessionItemLong)
840 ELSE NULL
841 END,
842 @length = CASE Locked
843 WHEN 0 THEN DATALENGTH(SessionItemLong)
844 ELSE NULL
845 END,
846 @locked = Locked,
847 Locked = 1
848 WHERE SessionId = @id
849 IF @length IS NOT NULL BEGIN
850 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
851 END
852
853 RETURN 0'
854
855 EXEC (@cmd)
856 GO
857
858
859 /*****************************************************************************/
860
861 DECLARE @ver int
862 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
863 DECLARE @cmd nchar(4000)
864 IF (@ver >= 8)
865 SET @cmd = N'
866 CREATE PROCEDURE dbo.TempGetStateItemExclusive3
867 @id tSessionId,
868 @itemShort tSessionItemShort OUTPUT,
869 @locked bit OUTPUT,
870 @lockAge int OUTPUT,
871 @lockCookie int OUTPUT,
872 @actionFlags int OUTPUT
873 AS
874 DECLARE @textptr AS tTextPtr
875 DECLARE @length AS int
876 DECLARE @now AS datetime
877 DECLARE @nowLocal AS datetime
878
879 SET @now = GETUTCDATE()
880 SET @nowLocal = GETDATE()
881
882 UPDATE [tempdb].dbo.ASPStateTempSessions
883 SET Expires = DATEADD(n, Timeout, @now),
884 LockDate = CASE Locked
885 WHEN 0 THEN @now
886 ELSE LockDate
887 END,
888 LockDateLocal = CASE Locked
889 WHEN 0 THEN @nowLocal
890 ELSE LockDateLocal
891 END,
892 @lockAge = CASE Locked
893 WHEN 0 THEN 0
894 ELSE DATEDIFF(second, LockDate, @now)
895 END,
896 @lockCookie = LockCookie = CASE Locked
897 WHEN 0 THEN LockCookie + 1
898 ELSE LockCookie
899 END,
900 @itemShort = CASE Locked
901 WHEN 0 THEN SessionItemShort
902 ELSE NULL
903 END,
904 @textptr = CASE Locked
905 WHEN 0 THEN TEXTPTR(SessionItemLong)
906 ELSE NULL
907 END,
908 @length = CASE Locked
909 WHEN 0 THEN DATALENGTH(SessionItemLong)
910 ELSE NULL
911 END,
912 @locked = Locked,
913 Locked = 1,
914
915 /* If the Uninitialized flag (0x1) if it is set,
916 remove it and return InitializeItem (0x1) in actionFlags */
917 Flags = CASE
918 WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
919 ELSE Flags
920 END,
921 @actionFlags = CASE
922 WHEN (Flags & 1) <> 0 THEN 1
923 ELSE 0
924 END
925 WHERE SessionId = @id
926 IF @length IS NOT NULL BEGIN
927 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
928 END
929
930 RETURN 0'
931 ELSE
932 SET @cmd = N'
933 CREATE PROCEDURE dbo.TempGetStateItemExclusive3
934 @id tSessionId,
935 @itemShort tSessionItemShort OUTPUT,
936 @locked bit OUTPUT,
937 @lockDate datetime OUTPUT,
938 @lockCookie int OUTPUT,
939 @actionFlags int OUTPUT
940 AS
941 DECLARE @textptr AS tTextPtr
942 DECLARE @length AS int
943 DECLARE @now AS datetime
944
945 SET @now = GETDATE()
946 UPDATE [tempdb].dbo.ASPStateTempSessions
947 SET Expires = DATEADD(n, Timeout, @now),
948 @lockDate = LockDate = CASE Locked
949 WHEN 0 THEN @now
950 ELSE LockDate
951 END,
952 @lockCookie = LockCookie = CASE Locked
953 WHEN 0 THEN LockCookie + 1
954 ELSE LockCookie
955 END,
956 @itemShort = CASE Locked
957 WHEN 0 THEN SessionItemShort
958 ELSE NULL
959 END,
960 @textptr = CASE Locked
961 WHEN 0 THEN TEXTPTR(SessionItemLong)
962 ELSE NULL
963 END,
964 @length = CASE Locked
965 WHEN 0 THEN DATALENGTH(SessionItemLong)
966 ELSE NULL
967 END,
968 @locked = Locked,
969 Locked = 1,
970
971 /* If the Uninitialized flag (0x1) if it is set,
972 remove it and return InitializeItem (0x1) in actionFlags */
973 Flags = CASE
974 WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
975 ELSE Flags
976 END,
977 @actionFlags = CASE
978 WHEN (Flags & 1) <> 0 THEN 1
979 ELSE 0
980 END
981 WHERE SessionId = @id
982 IF @length IS NOT NULL BEGIN
983 READTEXT [tempdb].dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
984 END
985
986 RETURN 0'
987
988 EXEC (@cmd)
989 GO
990
991
992 /*****************************************************************************/
993
994 DECLARE @ver int
995 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
996 DECLARE @cmd nchar(4000)
997 IF (@ver >= 8)
998 SET @cmd = N'
999 CREATE PROCEDURE dbo.TempReleaseStateItemExclusive
1000 @id tSessionId,
1001 @lockCookie int
1002 AS
1003 UPDATE [tempdb].dbo.ASPStateTempSessions
1004 SET Expires = DATEADD(n, Timeout, GETUTCDATE()),
1005 Locked = 0
1006 WHERE SessionId = @id AND LockCookie = @lockCookie
1007
1008 RETURN 0'
1009 ELSE
1010 SET @cmd = N'
1011 CREATE PROCEDURE dbo.TempReleaseStateItemExclusive
1012 @id tSessionId,
1013 @lockCookie int
1014 AS
1015 UPDATE [tempdb].dbo.ASPStateTempSessions
1016 SET Expires = DATEADD(n, Timeout, GETDATE()),
1017 Locked = 0
1018 WHERE SessionId = @id AND LockCookie = @lockCookie
1019
1020 RETURN 0'
1021
1022 EXEC (@cmd)
1023 GO
1024
1025
1026 /*****************************************************************************/
1027
1028 DECLARE @ver int
1029 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1030 DECLARE @cmd nchar(4000)
1031 IF (@ver >= 8)
1032 SET @cmd = N'
1033 CREATE PROCEDURE dbo.TempInsertUninitializedItem
1034 @id tSessionId,
1035 @itemShort tSessionItemShort,
1036 @timeout int
1037 AS
1038
1039 DECLARE @now AS datetime
1040 DECLARE @nowLocal AS datetime
1041
1042 SET @now = GETUTCDATE()
1043 SET @nowLocal = GETDATE()
1044
1045 INSERT [tempdb].dbo.ASPStateTempSessions
1046 (SessionId,
1047 SessionItemShort,
1048 Timeout,
1049 Expires,
1050 Locked,
1051 LockDate,
1052 LockDateLocal,
1053 LockCookie,
1054 Flags)
1055 VALUES
1056 (@id,
1057 @itemShort,
1058 @timeout,
1059 DATEADD(n, @timeout, @now),
1060 0,
1061 @now,
1062 @nowLocal,
1063 1,
1064 1)
1065
1066 RETURN 0'
1067 ELSE
1068 SET @cmd = N'
1069 CREATE PROCEDURE dbo.TempInsertUninitializedItem
1070 @id tSessionId,
1071 @itemShort tSessionItemShort,
1072 @timeout int
1073 AS
1074
1075 DECLARE @now AS datetime
1076 SET @now = GETDATE()
1077
1078 INSERT [tempdb].dbo.ASPStateTempSessions
1079 (SessionId,
1080 SessionItemShort,
1081 Timeout,
1082 Expires,
1083 Locked,
1084 LockDate,
1085 LockCookie,
1086 Flags)
1087 VALUES
1088 (@id,
1089 @itemShort,
1090 @timeout,
1091 DATEADD(n, @timeout, @now),
1092 0,
1093 @now,
1094 1,
1095 1)
1096
1097 RETURN 0'
1098
1099 EXEC (@cmd)
1100 GO
1101
1102
1103 /*****************************************************************************/
1104
1105 DECLARE @ver int
1106 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1107 DECLARE @cmd nchar(4000)
1108 IF (@ver >= 8)
1109 SET @cmd = N'
1110 CREATE PROCEDURE dbo.TempInsertStateItemShort
1111 @id tSessionId,
1112 @itemShort tSessionItemShort,
1113 @timeout int
1114 AS
1115
1116 DECLARE @now AS datetime
1117 DECLARE @nowLocal AS datetime
1118
1119 SET @now = GETUTCDATE()
1120 SET @nowLocal = GETDATE()
1121
1122 INSERT [tempdb].dbo.ASPStateTempSessions
1123 (SessionId,
1124 SessionItemShort,
1125 Timeout,
1126 Expires,
1127 Locked,
1128 LockDate,
1129 LockDateLocal,
1130 LockCookie)
1131 VALUES
1132 (@id,
1133 @itemShort,
1134 @timeout,
1135 DATEADD(n, @timeout, @now),
1136 0,
1137 @now,
1138 @nowLocal,
1139 1)
1140
1141 RETURN 0'
1142 ELSE
1143 SET @cmd = N'
1144 CREATE PROCEDURE dbo.TempInsertStateItemShort
1145 @id tSessionId,
1146 @itemShort tSessionItemShort,
1147 @timeout int
1148 AS
1149
1150 DECLARE @now AS datetime
1151 SET @now = GETDATE()
1152
1153 INSERT [tempdb].dbo.ASPStateTempSessions
1154 (SessionId,
1155 SessionItemShort,
1156 Timeout,
1157 Expires,
1158 Locked,
1159 LockDate,
1160 LockCookie)
1161 VALUES
1162 (@id,
1163 @itemShort,
1164 @timeout,
1165 DATEADD(n, @timeout, @now),
1166 0,
1167 @now,
1168 1)
1169
1170 RETURN 0'
1171
1172 EXEC (@cmd)
1173 GO
1174
1175
1176 /*****************************************************************************/
1177
1178 DECLARE @ver int
1179 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1180 DECLARE @cmd nchar(4000)
1181 IF (@ver >= 8)
1182 SET @cmd = N'
1183 CREATE PROCEDURE dbo.TempInsertStateItemLong
1184 @id tSessionId,
1185 @itemLong tSessionItemLong,
1186 @timeout int
1187 AS
1188 DECLARE @now AS datetime
1189 DECLARE @nowLocal AS datetime
1190
1191 SET @now = GETUTCDATE()
1192 SET @nowLocal = GETDATE()
1193
1194 INSERT [tempdb].dbo.ASPStateTempSessions
1195 (SessionId,
1196 SessionItemLong,
1197 Timeout,
1198 Expires,
1199 Locked,
1200 LockDate,
1201 LockDateLocal,
1202 LockCookie)
1203 VALUES
1204 (@id,
1205 @itemLong,
1206 @timeout,
1207 DATEADD(n, @timeout, @now),
1208 0,
1209 @now,
1210 @nowLocal,
1211 1)
1212
1213 RETURN 0'
1214 ELSE
1215 SET @cmd = N'
1216 CREATE PROCEDURE dbo.TempInsertStateItemLong
1217 @id tSessionId,
1218 @itemLong tSessionItemLong,
1219 @timeout int
1220 AS
1221 DECLARE @now AS datetime
1222 SET @now = GETDATE()
1223
1224 INSERT [tempdb].dbo.ASPStateTempSessions
1225 (SessionId,
1226 SessionItemLong,
1227 Timeout,
1228 Expires,
1229 Locked,
1230 LockDate,
1231 LockCookie)
1232 VALUES
1233 (@id,
1234 @itemLong,
1235 @timeout,
1236 DATEADD(n, @timeout, @now),
1237 0,
1238 @now,
1239 1)
1240
1241 RETURN 0'
1242
1243 EXEC (@cmd)
1244 GO
1245
1246
1247 /*****************************************************************************/
1248
1249 DECLARE @ver int
1250 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1251 DECLARE @cmd nchar(4000)
1252 IF (@ver >= 8)
1253 SET @cmd = N'
1254 CREATE PROCEDURE dbo.TempUpdateStateItemShort
1255 @id tSessionId,
1256 @itemShort tSessionItemShort,
1257 @timeout int,
1258 @lockCookie int
1259 AS
1260 UPDATE [tempdb].dbo.ASPStateTempSessions
1261 SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
1262 SessionItemShort = @itemShort,
1263 Timeout = @timeout,
1264 Locked = 0
1265 WHERE SessionId = @id AND LockCookie = @lockCookie
1266
1267 RETURN 0'
1268 ELSE
1269 SET @cmd = N'
1270 CREATE PROCEDURE dbo.TempUpdateStateItemShort
1271 @id tSessionId,
1272 @itemShort tSessionItemShort,
1273 @timeout int,
1274 @lockCookie int
1275 AS
1276 UPDATE [tempdb].dbo.ASPStateTempSessions
1277 SET Expires = DATEADD(n, @timeout, GETDATE()),
1278 SessionItemShort = @itemShort,
1279 Timeout = @timeout,
1280 Locked = 0
1281 WHERE SessionId = @id AND LockCookie = @lockCookie
1282
1283 RETURN 0'
1284
1285 EXEC (@cmd)
1286 GO
1287
1288
1289 /*****************************************************************************/
1290
1291 DECLARE @ver int
1292 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1293 DECLARE @cmd nchar(4000)
1294 IF (@ver >= 8)
1295 SET @cmd = N'
1296 CREATE PROCEDURE dbo.TempUpdateStateItemShortNullLong
1297 @id tSessionId,
1298 @itemShort tSessionItemShort,
1299 @timeout int,
1300 @lockCookie int
1301 AS
1302 UPDATE [tempdb].dbo.ASPStateTempSessions
1303 SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
1304 SessionItemShort = @itemShort,
1305 SessionItemLong = NULL,
1306 Timeout = @timeout,
1307 Locked = 0
1308 WHERE SessionId = @id AND LockCookie = @lockCookie
1309
1310 RETURN 0'
1311 ELSE
1312 SET @cmd = N'
1313 CREATE PROCEDURE dbo.TempUpdateStateItemShortNullLong
1314 @id tSessionId,
1315 @itemShort tSessionItemShort,
1316 @timeout int,
1317 @lockCookie int
1318 AS
1319 UPDATE [tempdb].dbo.ASPStateTempSessions
1320 SET Expires = DATEADD(n, @timeout, GETDATE()),
1321 SessionItemShort = @itemShort,
1322 SessionItemLong = NULL,
1323 Timeout = @timeout,
1324 Locked = 0
1325 WHERE SessionId = @id AND LockCookie = @lockCookie
1326
1327 RETURN 0'
1328
1329 EXEC (@cmd)
1330 GO
1331
1332
1333 /*****************************************************************************/
1334
1335 DECLARE @ver int
1336 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1337 DECLARE @cmd nchar(4000)
1338 IF (@ver >= 8)
1339 SET @cmd = N'
1340 CREATE PROCEDURE dbo.TempUpdateStateItemLong
1341 @id tSessionId,
1342 @itemLong tSessionItemLong,
1343 @timeout int,
1344 @lockCookie int
1345 AS
1346 UPDATE [tempdb].dbo.ASPStateTempSessions
1347 SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
1348 SessionItemLong = @itemLong,
1349 Timeout = @timeout,
1350 Locked = 0
1351 WHERE SessionId = @id AND LockCookie = @lockCookie
1352
1353 RETURN 0'
1354 ELSE
1355 SET @cmd = N'
1356 CREATE PROCEDURE dbo.TempUpdateStateItemLong
1357 @id tSessionId,
1358 @itemLong tSessionItemLong,
1359 @timeout int,
1360 @lockCookie int
1361 AS
1362 UPDATE [tempdb].dbo.ASPStateTempSessions
1363 SET Expires = DATEADD(n, @timeout, GETDATE()),
1364 SessionItemLong = @itemLong,
1365 Timeout = @timeout,
1366 Locked = 0
1367 WHERE SessionId = @id AND LockCookie = @lockCookie
1368
1369 RETURN 0'
1370
1371 EXEC (@cmd)
1372 GO
1373
1374
1375 /*****************************************************************************/
1376
1377 DECLARE @ver int
1378 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1379 DECLARE @cmd nchar(4000)
1380 IF (@ver >= 8)
1381 SET @cmd = N'
1382 CREATE PROCEDURE dbo.TempUpdateStateItemLongNullShort
1383 @id tSessionId,
1384 @itemLong tSessionItemLong,
1385 @timeout int,
1386 @lockCookie int
1387 AS
1388 UPDATE [tempdb].dbo.ASPStateTempSessions
1389 SET Expires = DATEADD(n, @timeout, GETUTCDATE()),
1390 SessionItemLong = @itemLong,
1391 SessionItemShort = NULL,
1392 Timeout = @timeout,
1393 Locked = 0
1394 WHERE SessionId = @id AND LockCookie = @lockCookie
1395
1396 RETURN 0'
1397 ELSE
1398 SET @cmd = N'
1399 CREATE PROCEDURE dbo.TempUpdateStateItemLongNullShort
1400 @id tSessionId,
1401 @itemLong tSessionItemLong,
1402 @timeout int,
1403 @lockCookie int
1404 AS
1405 UPDATE [tempdb].dbo.ASPStateTempSessions
1406 SET Expires = DATEADD(n, @timeout, GETDATE()),
1407 SessionItemLong = @itemLong,
1408 SessionItemShort = NULL,
1409 Timeout = @timeout,
1410 Locked = 0
1411 WHERE SessionId = @id AND LockCookie = @lockCookie
1412
1413 RETURN 0'
1414
1415 EXEC (@cmd)
1416 GO
1417
1418 /*****************************************************************************/
1419
1420 DECLARE @cmd nchar(4000)
1421 SET @cmd = N'
1422 CREATE PROCEDURE dbo.TempRemoveStateItem
1423 @id tSessionId,
1424 @lockCookie int
1425 AS
1426 DELETE [tempdb].dbo.ASPStateTempSessions
1427 WHERE SessionId = @id AND LockCookie = @lockCookie
1428 RETURN 0'
1429 EXEC(@cmd)
1430 GO
1431
1432 /*****************************************************************************/
1433
1434 DECLARE @ver int
1435 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1436 DECLARE @cmd nchar(4000)
1437 IF (@ver >= 8)
1438 SET @cmd = N'
1439 CREATE PROCEDURE dbo.TempResetTimeout
1440 @id tSessionId
1441 AS
1442 UPDATE [tempdb].dbo.ASPStateTempSessions
1443 SET Expires = DATEADD(n, Timeout, GETUTCDATE())
1444 WHERE SessionId = @id
1445 RETURN 0'
1446 ELSE
1447 SET @cmd = N'
1448 CREATE PROCEDURE dbo.TempResetTimeout
1449 @id tSessionId
1450 AS
1451 UPDATE [tempdb].dbo.ASPStateTempSessions
1452 SET Expires = DATEADD(n, Timeout, GETDATE())
1453 WHERE SessionId = @id
1454 RETURN 0'
1455
1456 EXEC (@cmd)
1457 GO
1458
1459
1460 /*****************************************************************************/
1461
1462 DECLARE @ver int
1463 EXEC dbo.GetMajorVersion @@ver=@ver OUTPUT
1464 DECLARE @cmd nchar(4000)
1465 IF (@ver >= 8)
1466 SET @cmd = N'
1467 CREATE PROCEDURE dbo.DeleteExpiredSessions
1468 AS
1469 SET NOCOUNT ON
1470 SET DEADLOCK_PRIORITY LOW
1471
1472 DECLARE @now datetime
1473 SET @now = GETUTCDATE()
1474
1475 CREATE TABLE #tblExpiredSessions
1476 (
1477 SessionID nvarchar(88) NOT NULL PRIMARY KEY
1478 )
1479
1480 INSERT #tblExpiredSessions (SessionID)
1481 SELECT SessionID
1482 FROM [tempdb].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
1483 WHERE Expires < @now
1484
1485 IF @@ROWCOUNT <> 0
1486 BEGIN
1487 DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
1488 FOR SELECT SessionID FROM #tblExpiredSessions
1489
1490 DECLARE @SessionID nvarchar(88)
1491
1492 OPEN ExpiredSessionCursor
1493
1494 FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
1495
1496 WHILE @@FETCH_STATUS = 0
1497 BEGIN
1498 DELETE FROM [tempdb].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
1499 FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
1500 END
1501
1502 CLOSE ExpiredSessionCursor
1503
1504 DEALLOCATE ExpiredSessionCursor
1505
1506 END
1507
1508 DROP TABLE #tblExpiredSessions
1509
1510 RETURN 0'
1511 ELSE
1512 SET @cmd = N'
1513 CREATE PROCEDURE dbo.DeleteExpiredSessions
1514 AS
1515 SET NOCOUNT ON
1516 SET DEADLOCK_PRIORITY LOW
1517
1518 DECLARE @now datetime
1519 SET @now = GETDATE()
1520
1521 CREATE TABLE #tblExpiredSessions
1522 (
1523 SessionID nvarchar(88) NOT NULL PRIMARY KEY
1524 )
1525
1526 INSERT #tblExpiredSessions (SessionID)
1527 SELECT SessionID
1528 FROM [tempdb].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
1529 WHERE Expires < @now
1530
1531 IF @@ROWCOUNT <> 0
1532 BEGIN
1533 DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
1534 FOR SELECT SessionID FROM #tblExpiredSessions
1535
1536 DECLARE @SessionID nvarchar(88)
1537
1538 OPEN ExpiredSessionCursor
1539
1540 FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
1541
1542 WHILE @@FETCH_STATUS = 0
1543 BEGIN
1544 DELETE FROM [tempdb].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
1545 FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
1546 END
1547
1548 CLOSE ExpiredSessionCursor
1549
1550 DEALLOCATE ExpiredSessionCursor
1551
1552 END
1553
1554 DROP TABLE #tblExpiredSessions
1555
1556 RETURN 0'
1557 EXEC (@cmd)
1558 GO
1559
1560 /*****************************************************************************/
1561
1562 EXECUTE dbo.CreateTempTables
1563 GO
1564
1565 USE master
1566 GO
1567
1568 DECLARE @sstype nvarchar(128)
1569 SET @sstype = N'sstype_temp'
1570
1571 IF UPPER(@sstype) = 'SSTYPE_TEMP' BEGIN
1572 DECLARE @cmd nchar(4000)
1573
1574 SET @cmd = N'
1575 /* Create the startup procedure */
1576 CREATE PROCEDURE dbo.ASPState_Startup
1577 AS
1578 EXECUTE ASPState.dbo.CreateTempTables
1579
1580 RETURN 0'
1581 EXEC(@cmd)
1582 EXECUTE sp_procoption @ProcName='dbo.ASPState_Startup', @OptionName='startup', @OptionValue='true'
1583 END
1584
1585 /*****************************************************************************/
1586
1587 /* Create the job to delete expired sessions */
1588
1589 -- Add job category
1590 -- We expect an error if the category already exists.
1591 PRINT 'If the category already exists, an error from msdb.dbo.sp_add_category is expected.'
1592 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
1593 GO
1594
1595 BEGIN TRANSACTION
1596 DECLARE @JobID BINARY(16)
1597 DECLARE @ReturnCode int
1598 DECLARE @nameT nchar(200)
1599 SELECT @ReturnCode = 0
1600
1601 -- Add the job
1602 SET @nameT = N'ASPState' + '_Job_DeleteExpiredSessions'
1603 EXECUTE @ReturnCode = msdb.dbo.sp_add_job
1604 @job_id = @JobID OUTPUT,
1605 @job_name = @nameT,
1606 @owner_login_name = NULL,
1607 @description = N'Deletes expired sessions from the session state database.',
1608 @category_name = N'[Uncategorized (Local)]',
1609 @enabled = 1,
1610 @notify_level_email = 0,
1611 @notify_level_page = 0,
1612 @notify_level_netsend = 0,
1613 @notify_level_eventlog = 0,
1614 @delete_level= 0
1615
1616 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1617
1618 -- Add the job steps
1619 SET @nameT = N'ASPState' + '_JobStep_DeleteExpiredSessions'
1620 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
1621 @job_id = @JobID,
1622 @step_id = 1,
1623 @step_name = @nameT,
1624 @command = N'EXECUTE DeleteExpiredSessions',
1625 @database_name = N'ASPState',
1626 @server = N'',
1627 @subsystem = N'TSQL',
1628 @cmdexec_success_code = 0,
1629 @flags = 0,
1630 @retry_attempts = 0,
1631 @retry_interval = 1,
1632 @output_file_name = N'',
1633 @on_success_step_id = 0,
1634 @on_success_action = 1,
1635 @on_fail_step_id = 0,
1636 @on_fail_action = 2
1637
1638 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1639
1640 EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
1641 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1642
1643 -- Add the job schedules
1644 SET @nameT = N'ASPState' + '_JobSchedule_DeleteExpiredSessions'
1645 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
1646 @job_id = @JobID,
1647 @name = @nameT,
1648 @enabled = 1,
1649 @freq_type = 4,
1650 @active_start_date = 20001016,
1651 @active_start_time = 0,
1652 @freq_interval = 1,
1653 @freq_subday_type = 4,
1654 @freq_subday_interval = 1,
1655 @freq_relative_interval = 0,
1656 @freq_recurrence_factor = 0,
1657 @active_end_date = 99991231,
1658 @active_end_time = 235959
1659
1660 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1661
1662 -- Add the Target Servers
1663 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
1664 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1665
1666 COMMIT TRANSACTION
1667 GOTO EndSave
1668 QuitWithRollback:
1669 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
1670 EndSave:
1671 GO
1672
1673 /*************************************************************/
1674 /*************************************************************/
1675 /*************************************************************/
1676 /*************************************************************/
1677
1678 PRINT ''
1679 PRINT '------------------------------------------'
1680 PRINT 'Completed execution of InstallSqlState.SQL'
1681 PRINT '------------------------------------------'
，之后再打开，我就可以看到
,出现
就很容易看出来。
扫一扫
2278
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
