1/*====================================================================================== 2Microsoft SQL Server Sample Code 3 4Copyright (c) Microsoft Corporation 5 6All rights reserved. 7 8MIT License. 9 10Permission is hereby granted, free of charge, to any person obtaining a copy 11of this software and associated documentation files (the "Software"), to 12deal in the Software without restriction, including without limitation the 13rights to use, copy, modify, merge, publish, distribute, sublicense, and/or 14sell copies of the Software, and to permit persons to whom the Software is 15furnished to do so, subject to the following conditions: 16 17The above copyright notice and this permission notice shall be included in 18all copies or substantial portions of the Software. 19 20THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 21IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 22FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 23AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 24LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 25FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS 26IN THE SOFTWARE. 27 28Script: aspstate_sql2016_with_retry.sql 29 30Description: 31This script is based on the InstallSqlState.sql script but works with SQL 2016 In-Memory OLTP by replacing the following objects 32to in-memory and natively compiled stored procedures. 33 34** Tables: 35 Converted the following table to In-Memory table: 36 - [dbo].[ASPStateTempSessions] (SessionId: NONCLUSTERED HASH PK (Bucket Count=33554432)) 37 38** Stored Procedures: 39 Converted the following SPs to Native Compiled SPs: 40 - dbo.TempGetStateItemExclusive3 41 - dbo.TempInsertStateItemShort 42 - dbo.TempUpdateStateItemLong 43 - dbo.TempUpdateStateItemLongNullShort 44 - dbo.TempUpdateStateItemShort 45======================================================================================*/ 46 47USE [master] 48GO 49 50DECLARE @SQLDataFolder nvarchar(max) = cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar(max)) 51DECLARE @SQL nvarchar(max) = N''; 52 53SET @SQL = N' 54CREATE DATABASE [ASPState] 55 CONTAINMENT = NONE 56 ON PRIMARY 57 (NAME = N''ASPState'', FILENAME = N''' + @SQLDataFolder + 'ASPState.mdf'' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 58 FILEGROUP [ASPState_mod] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT 59 (NAME = N''ASPState_mod'', FILENAME = N''' + @SQLDataFolder + 'ASPState_mod'' , MAXSIZE = UNLIMITED) 60 LOG ON 61 (NAME = N''ASPState_log'', FILENAME = N''' + @SQLDataFolder + 'ASPState_log.ldf'' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ); 62 63ALTER DATABASE [ASPState] SET COMPATIBILITY_LEVEL = 130; ALTER DATABASE [ASPState] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;' 64 65EXECUTE (@SQL) 66GO 67 68USE [AspState] 69GO 70 71CREATE ROLE [ASPStateExecute]; 72CREATE ROLE [ASPStateResetRole]; 73CREATE ROLE [ASPStateRole]; 74 75CREATE TYPE [dbo].[tAppName] FROM [varchar](280) NOT NULL; 76CREATE TYPE [dbo].[tSessionId] FROM [nvarchar](88) NOT NULL; 77CREATE TYPE [dbo].[tSessionItemLong] FROM [image] NULL; 78CREATE TYPE [dbo].[tSessionItemShort] FROM [varbinary](7000) NULL; 79CREATE TYPE [dbo].[tTextPtr] FROM [varbinary](max) NULL; 80 81 82CREATE TABLE [dbo].[ASPStateTempSessions] 83( 84 [SessionId] [nvarchar](88) COLLATE Latin1_General_100_BIN2 NOT NULL, 85 [Created] [datetime] NOT NULL DEFAULT (getutcdate()), 86 [Expires] [datetime] NOT NULL, 87 [LockDate] [datetime] NOT NULL, 88 [LockDateLocal] [datetime] NOT NULL, 89 [LockCookie] [int] NOT NULL, 90 [Timeout] [int] NOT NULL, 91 [Locked] [bit] NOT NULL, 92 [SessionItemShort] [varbinary](7000) NULL, 93 [SessionItemLong] [varbinary](max) NULL, 94 [Flags] [int] NOT NULL DEFAULT ((0)), 95 96INDEX [Index_Expires] NONCLUSTERED 97( 98 [Expires] ASC 99), 100PRIMARY KEY NONCLUSTERED HASH 101( 102 [SessionId] 103)WITH ( BUCKET_COUNT = 33554432) 104)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY ) 105GO 106 107CREATE TABLE [dbo].[ASPStateTempApplications]( 108 [AppId] [int] NOT NULL, 109 [AppName] [char](280) NOT NULL, 110PRIMARY KEY CLUSTERED 111( 112 [AppId] ASC 113)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 114) ON [PRIMARY] 115 116GO 117 118CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3_HK] 119 @id nvarchar(88), 120 @itemShort varbinary(7000) OUTPUT, 121 @locked bit OUTPUT, 122 @lockAge int OUTPUT, 123 @lockCookie int OUTPUT, 124 @actionFlags int OUTPUT 125WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 126AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') 127 128 DECLARE @textptr AS varbinary(max) 129 DECLARE @length AS int 130 DECLARE @now AS datetime 131 DECLARE @nowLocal AS datetime 132 133 SET @now = GETUTCDATE() 134 SET @nowLocal = GETDATE() 135 136 DECLARE @LockedCheck bit 137 DECLARE @Flags int 138 139 SELECT @LockedCheck=Locked, @Flags=Flags FROM dbo.ASPStateTempSessions WHERE SessionID=@id 140 141 IF @Flags&1 <> 0 142 BEGIN 143 SET @actionFlags=1 144 UPDATE dbo.ASPStateTempSessions SET Flags=Flags& ~1 WHERE SessionID=@id 145 END 146 ELSE 147 SET @actionFlags=0 148 149 IF @LockedCheck=1 150 BEGIN 151 UPDATE dbo.ASPStateTempSessions 152 SET Expires = DATEADD(n, Timeout, @now), 153 @lockAge = DATEDIFF(second, LockDate, @now), 154 @lockCookie = LockCookie, 155 @itemShort = NULL, 156 --@textptr = NULL, 157 @length = NULL, 158 @locked = 1 159 WHERE SessionId = @id 160 END 161 ELSE 162 BEGIN 163 UPDATE dbo.ASPStateTempSessions 164 SET Expires = DATEADD(n, Timeout, @now), 165 LockDate = @now, 166 LockDateLocal = @nowlocal, 167 @lockAge = 0, 168 @lockCookie = LockCookie = LockCookie + 1, 169 @itemShort = SessionItemShort, 170 @textptr = SessionItemLong, 171 @length = 1, 172 @locked = 0, 173 Locked = 1 174 WHERE SessionId = @id 175 176 IF @TextPtr IS NOT NULL 177 SELECT @TextPtr 178 179 END 180END 181GO 182 183CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3] 184 @id nvarchar(88), 185 @itemShort varbinary(7000) OUTPUT, 186 @locked bit OUTPUT, 187 @lockAge int OUTPUT, 188 @lockCookie int OUTPUT, 189 @actionFlags int OUTPUT 190AS 191BEGIN 192 DECLARE @retry INT = 10; 193 194 WHILE (@retry > 0) 195 BEGIN 196 BEGIN TRY 197 BEGIN TRANSACTION; 198 EXEC dbo.TempGetStateItemExclusive3_HK 199 @id, 200 @itemShort = @itemShort OUTPUT, 201 @locked = @locked OUTPUT, 202 @lockAge = @lockAge OUTPUT, 203 @lockCookie = @lockCookie OUTPUT, 204 @actionFlags = @actionFlags OUTPUT 205 206 COMMIT TRANSACTION; 207 SET @retry = 0; -- //Stops the loop. 208 END TRY 209 210 BEGIN CATCH 211 SET @retry -= 1; 212 213 IF (@retry > 0 AND 214 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 215 ) 216 BEGIN 217 IF XACT_STATE() = -1 218 ROLLBACK TRANSACTION; 219 220 WAITFOR DELAY '00:00:00.001'; 221 END 222 ELSE 223 BEGIN 224 PRINT 'Suffered an error for which Retry is inappropriate.'; 225 THROW; 226 END 227 END CATCH 228 229 END -- //While loop 230END; 231GO 232 233CREATE PROCEDURE [dbo].[TempInsertStateItemShort_HK] 234 @id nvarchar(88), 235 @itemShort varbinary(7000), 236 @timeout int 237WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 238AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') 239 240 DECLARE @now AS datetime 241 DECLARE @nowLocal AS datetime 242 243 SET @now = GETUTCDATE() 244 SET @nowLocal = GETDATE() 245 246 INSERT dbo.ASPStateTempSessions 247 (SessionId, 248 SessionItemShort, 249 Timeout, 250 Expires, 251 Locked, 252 LockDate, 253 LockDateLocal, 254 LockCookie, 255 Created, 256 Flags, 257 SessionItemLong) 258 VALUES 259 (@id, 260 @itemShort, 261 @timeout, 262 DATEADD(n, @timeout, @now), 263 0, 264 @now, 265 @nowLocal, 266 1, 267 @now, 268 0, 269 NULL) 270 271 RETURN 0 272END 273GO 274 275CREATE PROCEDURE [dbo].[TempInsertStateItemShort] 276 @id nvarchar(88), 277 @itemShort varbinary(7000), 278 @timeout int 279AS 280BEGIN 281 DECLARE @retry INT = 10; 282 283 WHILE (@retry > 0) 284 BEGIN 285 BEGIN TRY 286 BEGIN TRANSACTION; 287 EXEC dbo.TempInsertStateItemShort_HK @id, @itemShort, @timeout 288 289 COMMIT TRANSACTION; 290 SET @retry = 0; -- //Stops the loop. 291 END TRY 292 293 BEGIN CATCH 294 SET @retry -= 1; 295 296 IF (@retry > 0 AND 297 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 298 ) 299 BEGIN 300 IF XACT_STATE() = -1 301 ROLLBACK TRANSACTION; 302 303 WAITFOR DELAY '00:00:00.001'; 304 END 305 ELSE 306 BEGIN 307 PRINT 'Suffered an error for which Retry is inappropriate.'; 308 THROW; 309 END 310 END CATCH 311 312 END -- //While loop 313END; 314GO 315 316CREATE PROCEDURE [dbo].[TempUpdateStateItemLong_HK] 317 @id nvarchar(88), 318 @itemLong varbinary(max), 319 @timeout int, 320 @lockCookie int 321WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 322AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') 323 324 UPDATE dbo.ASPStateTempSessions 325 SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 326 SessionItemLong = @itemLong, 327 Timeout = @timeout, 328 Locked = 0 329 WHERE SessionId = @id AND LockCookie = @lockCookie 330 331 RETURN 0 332END 333GO 334 335CREATE PROCEDURE [dbo].[TempUpdateStateItemLong] 336 @id nvarchar(88), 337 @itemLong varbinary(max), 338 @timeout int, 339 @lockCookie int 340AS 341BEGIN 342 DECLARE @retry INT = 10; 343 344 WHILE (@retry > 0) 345 BEGIN 346 BEGIN TRY 347 BEGIN TRANSACTION; 348 EXEC dbo.TempUpdateStateItemLong_HK @id, @itemLong, @timeout, @lockCookie 349 350 COMMIT TRANSACTION; 351 SET @retry = 0; -- //Stops the loop. 352 END TRY 353 354 BEGIN CATCH 355 SET @retry -= 1; 356 357 IF (@retry > 0 AND 358 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 359 ) 360 BEGIN 361 IF XACT_STATE() = -1 362 ROLLBACK TRANSACTION; 363 364 WAITFOR DELAY '00:00:00.001'; 365 END 366 ELSE 367 BEGIN 368 PRINT 'Suffered an error for which Retry is inappropriate.'; 369 THROW; 370 END 371 END CATCH 372 373 END -- //While loop 374END; 375GO 376 377CREATE PROCEDURE [dbo].[TempUpdateStateItemLongNullShort_HK] 378 @id nvarchar(88), 379 @itemLong varbinary(max), 380 @timeout int, 381 @lockCookie int 382WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 383AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') 384 385 UPDATE dbo.ASPStateTempSessions 386 SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 387 SessionItemLong = @itemLong, 388 SessionItemShort = NULL, 389 Timeout = @timeout, 390 Locked = 0 391 WHERE SessionId = @id AND LockCookie = @lockCookie 392 393 RETURN 0 394END 395GO 396 397CREATE PROCEDURE [dbo].[TempUpdateStateItemLongNullShort] 398 @id nvarchar(88), 399 @itemLong varbinary(max), 400 @timeout int, 401 @lockCookie int 402AS 403BEGIN 404 DECLARE @retry INT = 10; 405 406 WHILE (@retry > 0) 407 BEGIN 408 BEGIN TRY 409 BEGIN TRANSACTION; 410 EXEC dbo.TempUpdateStateItemLongNullShort_HK @id, @itemLong, @timeout, @lockCookie 411 412 COMMIT TRANSACTION; 413 SET @retry = 0; -- //Stops the loop. 414 END TRY 415 416 BEGIN CATCH 417 SET @retry -= 1; 418 419 IF (@retry > 0 AND 420 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 421 ) 422 BEGIN 423 IF XACT_STATE() = -1 424 ROLLBACK TRANSACTION; 425 426 WAITFOR DELAY '00:00:00.001'; 427 END 428 ELSE 429 BEGIN 430 PRINT 'Suffered an error for which Retry is inappropriate.'; 431 THROW; 432 END 433 END CATCH 434 435 END -- //While loop 436END; 437GO 438 439CREATE PROCEDURE [dbo].[TempUpdateStateItemShort_HK] 440 @id nvarchar(88), 441 @itemShort varbinary(7000), 442 @timeout int, 443 @lockCookie int 444WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 445AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) 446 447 UPDATE dbo.ASPStateTempSessions 448 SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 449 SessionItemShort = @itemShort, 450 Timeout = @timeout, 451 Locked = 0 452 WHERE SessionId = @id AND LockCookie = @lockCookie 453 454 RETURN 0 455END 456GO 457 458CREATE PROCEDURE [dbo].[TempUpdateStateItemShort] 459 @id nvarchar(88), 460 @itemShort varbinary(max), 461 @timeout int, 462 @lockCookie int 463AS 464BEGIN 465 DECLARE @retry INT = 10; 466 467 WHILE (@retry > 0) 468 BEGIN 469 BEGIN TRY 470 BEGIN TRANSACTION; 471 EXEC dbo.TempUpdateStateItemShort_HK @id, @itemShort, @timeout, @lockCookie 472 473 COMMIT TRANSACTION; 474 SET @retry = 0; -- //Stops the loop. 475 END TRY 476 477 BEGIN CATCH 478 SET @retry -= 1; 479 480 IF (@retry > 0 AND 481 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 482 ) 483 BEGIN 484 IF XACT_STATE() = -1 485 ROLLBACK TRANSACTION; 486 487 WAITFOR DELAY '00:00:00.001'; 488 END 489 ELSE 490 BEGIN 491 PRINT 'Suffered an error for which Retry is inappropriate.'; 492 THROW; 493 END 494 END CATCH 495 496 END -- //While loop 497END; 498GO 499 500CREATE PROCEDURE [dbo].[CreateTempTables] 501AS 502 CREATE TABLE ASPStateTempSessions ( 503 SessionId nvarchar(88) COLLATE Latin1_General_100_BIN2 NOT NULL, 504 Created datetime NOT NULL DEFAULT GETUTCDATE(), 505 Expires datetime NOT NULL, 506 LockDate datetime NOT NULL, 507 LockDateLocal datetime NOT NULL, 508 LockCookie int NOT NULL, 509 Timeout int NOT NULL, 510 Locked bit NOT NULL, 511 SessionItemShort VARBINARY(7000) NULL, 512 SessionItemLong VARBINARY(max) NULL, 513 Flags int NOT NULL DEFAULT 0, 514 515 PRIMARY KEY NONCLUSTERED HASH 516 ( 517 [SessionId] 518 )WITH ( BUCKET_COUNT = 33554432), 519 INDEX Index_Expires (Expires) 520 521 )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY ) 522 523 CREATE TABLE dbo.ASPStateTempApplications ( 524 AppId int NOT NULL PRIMARY KEY, 525 AppName char(280) NOT NULL, 526 ) 527 CREATE NONCLUSTERED INDEX Index_AppName ON ASPStateTempApplications(AppName) 528 529RETURN 0 530GO 531 532CREATE PROCEDURE [dbo].[DeleteExpiredSessions] 533AS 534 SET NOCOUNT ON 535 SET DEADLOCK_PRIORITY LOW 536 537 DECLARE @now datetime 538 SET @now = GETUTCDATE() 539 540 CREATE TABLE #tblExpiredSessions 541 ( 542 SessionId nvarchar(88) NOT NULL PRIMARY KEY 543 ) 544 545 INSERT #tblExpiredSessions (SessionId) 546 SELECT SessionId 547 FROM ASPStateTempSessions WITH (SNAPSHOT) 548 WHERE Expires < @now 549 550 IF @@ROWCOUNT <> 0 551 BEGIN 552 DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY 553 FOR SELECT SessionId FROM #tblExpiredSessions 554 555 DECLARE @SessionId nvarchar(88) 556 557 OPEN ExpiredSessionCursor 558 559 FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId 560 561 WHILE @@FETCH_STATUS = 0 562 BEGIN 563 DELETE FROM ASPStateTempSessions WHERE SessionId = @SessionId AND Expires < @now 564 FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId 565 END 566 567 CLOSE ExpiredSessionCursor 568 569 DEALLOCATE ExpiredSessionCursor 570 571 END 572 573 DROP TABLE #tblExpiredSessions 574 575RETURN 0 576GO 577 578CREATE PROCEDURE [dbo].[GetHashCode] 579 @input tAppName, 580 @hash int OUTPUT 581AS 582 /* 583 This sproc is based on this C# hash function: 584 585 int GetHashCode(string s) 586 { 587 int hash = 5381; 588 int len = s.Length; 589 590 for (int i = 0; i < len; i++) { 591 int c = Convert.ToInt32(s[i]); 592 hash = ((hash << 5) + hash) ^ c; 593 } 594 595 return hash; 596 } 597 598 However, SQL 7 doesn't provide a 32-bit integer 599 type that allows rollover of bits, we have to 600 divide our 32bit integer into the upper and lower 601 16 bits to do our calculation. 602 */ 603 604 DECLARE @hi_16bit int 605 DECLARE @lo_16bit int 606 DECLARE @hi_t int 607 DECLARE @lo_t int 608 DECLARE @len int 609 DECLARE @i int 610 DECLARE @c int 611 DECLARE @carry int 612 613 SET @hi_16bit = 0 614 SET @lo_16bit = 5381 615 616 SET @len = DATALENGTH(@input) 617 SET @i = 1 618 619 WHILE (@i <= @len) 620 BEGIN 621 SET @c = ASCII(SUBSTRING(@input, @i, 1)) 622 623 /* Formula: 624 hash = ((hash << 5) + hash) ^ c */ 625 626 /* hash << 5 */ 627 SET @hi_t = @hi_16bit * 32 /* high 16bits << 5 */ 628 SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */ 629 630 SET @lo_t = @lo_16bit * 32 /* low 16bits << 5 */ 631 632 SET @carry = @lo_16bit & 0x1F0000 /* move low 16bits carryover to hi 16bits */ 633 SET @carry = @carry / 0x10000 /* >> 16 */ 634 SET @hi_t = @hi_t + @carry 635 SET @hi_t = @hi_t & 0xFFFF /* zero out overflow */ 636 637 /* + hash */ 638 SET @lo_16bit = @lo_16bit + @lo_t 639 SET @hi_16bit = @hi_16bit + @hi_t + (@lo_16bit / 0x10000) 640 /* delay clearing the overflow */ 641 642 /* ^c */ 643 SET @lo_16bit = @lo_16bit ^ @c 644 645 /* Now clear the overflow bits */ 646 SET @hi_16bit = @hi_16bit & 0xFFFF 647 SET @lo_16bit = @lo_16bit & 0xFFFF 648 649 SET @i = @i + 1 650 END 651 652 /* Do a sign extension of the hi-16bit if needed */ 653 IF (@hi_16bit & 0x8000 <> 0) 654 SET @hi_16bit = 0xFFFF0000 | @hi_16bit 655 656 /* Merge hi and lo 16bit back together */ 657 SET @hi_16bit = @hi_16bit * 0x10000 /* << 16 */ 658 SET @hash = @hi_16bit | @lo_16bit 659 660 RETURN 0 661 662GO 663 664CREATE PROCEDURE [dbo].[GetMajorVersion] 665 @@ver int OUTPUT 666AS 667BEGIN 668 DECLARE @version nchar(100) 669 DECLARE @dot int 670 DECLARE @hyphen int 671 DECLARE @SqlToExec nchar(4000) 672 673 SELECT @@ver = 7 674 SELECT @version = @@Version 675 SELECT @hyphen = CHARINDEX(N' - ', @version) 676 IF (NOT(@hyphen IS NULL) AND @hyphen > 0) 677 BEGIN 678 SELECT @hyphen = @hyphen + 3 679 SELECT @dot = CHARINDEX(N'.', @version, @hyphen) 680 IF (NOT(@dot IS NULL) AND @dot > @hyphen) 681 BEGIN 682 SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen) 683 SELECT @@ver = CONVERT(int, @version) 684 END 685 END 686END 687GO 688 689CREATE PROCEDURE [dbo].[TempGetAppID] 690 @appName VARCHAR(280), --@appName tAppName, 691 @appId int OUTPUT 692AS 693 SET @appName = LOWER(@appName) 694 SET @appId = NULL 695 696 SELECT @appId = AppId 697 FROM ASPStateTempApplications 698 WHERE AppName = @appName 699 700 IF @appId IS NULL BEGIN 701 BEGIN TRAN 702 703 SELECT @appId = AppId 704 FROM ASPStateTempApplications WITH (TABLOCKX) 705 WHERE AppName = @appName 706 707 IF @appId IS NULL 708 BEGIN 709 EXEC GetHashCode @appName, @appId OUTPUT 710 711 INSERT ASPStateTempApplications 712 VALUES 713 (@appId, @appName) 714 715 IF @@ERROR = 2627 716 BEGIN 717 DECLARE @dupApp tAppName 718 719 SELECT @dupApp = RTRIM(AppName) 720 FROM ASPStateTempApplications 721 WHERE AppId = @appId 722 723 RAISERROR('SQL session state fatal error: hash-code collision between applications ''%s'' and ''%s''. Please rename the 1st application to resolve the problem.', 724 18, 1, @appName, @dupApp) 725 END 726 END 727 COMMIT 728 END 729 730RETURN 0 731 732GO 733 734CREATE PROCEDURE [dbo].[TempGetStateItem] 735 @id tSessionId, 736 @itemShort tSessionItemShort OUTPUT, 737 @locked bit OUTPUT, 738 @lockDate datetime OUTPUT, 739 @lockCookie int OUTPUT 740AS 741 DECLARE @textptr AS tTextPtr 742 DECLARE @length AS int 743 DECLARE @now AS datetime 744 SET @now = GETUTCDATE() 745 746 DECLARE @retry INT = 10; 747 748 WHILE (@retry > 0) 749 BEGIN 750 BEGIN TRY 751 BEGIN TRANSACTION; 752 753 UPDATE ASPStateTempSessions 754 SET Expires = DATEADD(n, Timeout, @now), 755 @locked = Locked, 756 @lockDate = LockDateLocal, 757 @lockCookie = LockCookie, 758 @itemShort = CASE @locked 759 WHEN 0 THEN SessionItemShort 760 ELSE NULL 761 END, 762 @textptr = CASE @locked 763 WHEN 0 THEN SessionItemLong 764 ELSE NULL 765 END, 766 @length = CASE @locked 767 WHEN 0 THEN DATALENGTH(SessionItemLong) 768 ELSE NULL 769 END 770 WHERE SessionId = @id 771 IF @length IS NOT NULL BEGIN 772 SELECT @textptr 773 END 774 COMMIT TRANSACTION; 775 SET @retry = 0; -- //Stops the loop. 776 END TRY 777 778 BEGIN CATCH 779 SET @retry -= 1; 780 781 IF (@retry > 0 AND 782 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 783 ) 784 BEGIN 785 IF XACT_STATE() = -1 786 ROLLBACK TRANSACTION; 787 788 WAITFOR DELAY '00:00:00.001'; 789 END 790 ELSE 791 BEGIN 792 PRINT 'Suffered an error for which Retry is inappropriate.'; 793 THROW; 794 END 795 END CATCH 796 797 END -- //While loop 798 RETURN 0 799GO 800 801CREATE PROCEDURE [dbo].[TempGetStateItem2] 802 @id tSessionId, 803 @itemShort tSessionItemShort OUTPUT, 804 @locked bit OUTPUT, 805 @lockAge int OUTPUT, 806 @lockCookie int OUTPUT 807AS 808 DECLARE @textptr AS tTextPtr 809 DECLARE @length AS int 810 DECLARE @now AS datetime 811 SET @now = GETUTCDATE() 812 813 DECLARE @retry INT = 10; 814 815 WHILE (@retry > 0) 816 BEGIN 817 BEGIN TRY 818 BEGIN TRANSACTION; 819 UPDATE ASPStateTempSessions 820 SET Expires = DATEADD(n, Timeout, @now), 821 @locked = Locked, 822 @lockAge = DATEDIFF(second, LockDate, @now), 823 @lockCookie = LockCookie, 824 @itemShort = CASE @locked 825 WHEN 0 THEN SessionItemShort 826 ELSE NULL 827 END, 828 @textptr = CASE @locked 829 WHEN 0 THEN SessionItemLong 830 ELSE NULL 831 END, 832 @length = CASE @locked 833 WHEN 0 THEN DATALENGTH(SessionItemLong) 834 ELSE NULL 835 END 836 WHERE SessionId = @id 837 IF @length IS NOT NULL BEGIN 838 SELECT @textptr 839 END 840 COMMIT TRANSACTION; 841 SET @retry = 0; -- //Stops the loop. 842 END TRY 843 844 BEGIN CATCH 845 SET @retry -= 1; 846 847 IF (@retry > 0 AND 848 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 849 ) 850 BEGIN 851 IF XACT_STATE() = -1 852 ROLLBACK TRANSACTION; 853 854 WAITFOR DELAY '00:00:00.001'; 855 END 856 ELSE 857 BEGIN 858 PRINT 'Suffered an error for which Retry is inappropriate.'; 859 THROW; 860 END 861 END CATCH 862 863 END -- //While loop 864 RETURN 0 865GO 866 867CREATE PROCEDURE [dbo].[TempGetStateItem3] 868 @id nvarchar(88), 869 @itemShort varbinary(7000) OUTPUT, 870 @locked bit OUTPUT, 871 @lockAge int OUTPUT, 872 @lockCookie int OUTPUT, 873 @actionFlags int OUTPUT 874AS 875 DECLARE @textptr AS tTextPtr 876 DECLARE @length AS int 877 DECLARE @now AS datetime 878 SET @now = GETUTCDATE() 879 880 DECLARE @retry INT = 10; 881 882 WHILE (@retry > 0) 883 BEGIN 884 BEGIN TRY 885 BEGIN TRANSACTION; 886 UPDATE ASPStateTempSessions 887 SET Expires = DATEADD(n, Timeout, @now), 888 @locked = Locked, 889 @lockAge = DATEDIFF(second, LockDate, @now), 890 @lockCookie = LockCookie, 891 @itemShort = CASE @locked 892 WHEN 0 THEN SessionItemShort 893 ELSE NULL 894 END, 895 @textptr = CASE @locked 896 WHEN 0 THEN SessionItemLong 897 ELSE NULL 898 END, 899 @length = CASE @locked 900 WHEN 0 THEN DATALENGTH(SessionItemLong) 901 ELSE NULL 902 END, 903 904 /* If the Uninitialized flag (0x1) if it is set, 905 remove it and return InitializeItem (0x1) in actionFlags */ 906 Flags = CASE 907 WHEN (Flags & 1) <> 0 THEN (Flags & ~1) 908 ELSE Flags 909 END, 910 @actionFlags = CASE 911 WHEN (Flags & 1) <> 0 THEN 1 912 ELSE 0 913 END 914 WHERE SessionId = @id 915 IF @length IS NOT NULL BEGIN 916 SELECT @textptr 917 END 918 COMMIT TRANSACTION; 919 SET @retry = 0; -- //Stops the loop. 920 END TRY 921 922 BEGIN CATCH 923 SET @retry -= 1; 924 925 IF (@retry > 0 AND 926 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 927 ) 928 BEGIN 929 IF XACT_STATE() = -1 930 ROLLBACK TRANSACTION; 931 932 WAITFOR DELAY '00:00:00.001'; 933 END 934 ELSE 935 BEGIN 936 PRINT 'Suffered an error for which Retry is inappropriate.'; 937 THROW; 938 END 939 END CATCH 940 941 END -- //While loop 942 RETURN 0 943GO 944 945CREATE PROCEDURE [dbo].[TempGetStateItemExclusive] 946 @id tSessionId, 947 @itemShort tSessionItemShort OUTPUT, 948 @locked bit OUTPUT, 949 @lockDate datetime OUTPUT, 950 @lockCookie int OUTPUT 951AS 952 DECLARE @textptr AS tTextPtr 953 DECLARE @length AS int 954 DECLARE @now AS datetime 955 DECLARE @nowLocal AS datetime 956 957 SET @now = GETUTCDATE() 958 SET @nowLocal = GETDATE() 959 960 DECLARE @retry INT = 10; 961 962 WHILE (@retry > 0) 963 BEGIN 964 BEGIN TRY 965 BEGIN TRANSACTION; 966 UPDATE ASPStateTempSessions 967 SET Expires = DATEADD(n, Timeout, @now), 968 LockDate = CASE Locked 969 WHEN 0 THEN @now 970 ELSE LockDate 971 END, 972 @lockDate = LockDateLocal = CASE Locked 973 WHEN 0 THEN @nowLocal 974 ELSE LockDateLocal 975 END, 976 @lockCookie = LockCookie = CASE Locked 977 WHEN 0 THEN LockCookie + 1 978 ELSE LockCookie 979 END, 980 @itemShort = CASE Locked 981 WHEN 0 THEN SessionItemShort 982 ELSE NULL 983 END, 984 @textptr = CASE Locked 985 WHEN 0 THEN SessionItemLong 986 ELSE NULL 987 END, 988 @length = CASE Locked 989 WHEN 0 THEN DATALENGTH(SessionItemLong) 990 ELSE NULL 991 END, 992 @locked = Locked, 993 Locked = 1 994 WHERE SessionId = @id 995 IF @length IS NOT NULL BEGIN 996 SELECT @textptr 997 END 998 COMMIT TRANSACTION; 999 SET @retry = 0; -- //Stops the loop. 1000 END TRY 1001 1002 BEGIN CATCH 1003 SET @retry -= 1; 1004 1005 IF (@retry > 0 AND 1006 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1007 ) 1008 BEGIN 1009 IF XACT_STATE() = -1 1010 ROLLBACK TRANSACTION; 1011 1012 WAITFOR DELAY '00:00:00.001'; 1013 END 1014 ELSE 1015 BEGIN 1016 PRINT 'Suffered an error for which Retry is inappropriate.'; 1017 THROW; 1018 END 1019 END CATCH 1020 1021 END -- //While loop 1022 RETURN 0 1023GO 1024 1025CREATE PROCEDURE [dbo].[TempGetStateItemExclusive2] 1026 @id tSessionId, 1027 @itemShort tSessionItemShort OUTPUT, 1028 @locked bit OUTPUT, 1029 @lockAge int OUTPUT, 1030 @lockCookie int OUTPUT 1031AS 1032 DECLARE @textptr AS tTextPtr 1033 DECLARE @length AS int 1034 DECLARE @now AS datetime 1035 DECLARE @nowLocal AS datetime 1036 1037 SET @now = GETUTCDATE() 1038 SET @nowLocal = GETDATE() 1039 1040 DECLARE @retry INT = 10; 1041 1042 WHILE (@retry > 0) 1043 BEGIN 1044 BEGIN TRY 1045 BEGIN TRANSACTION; 1046 UPDATE ASPStateTempSessions 1047 SET Expires = DATEADD(n, Timeout, @now), 1048 LockDate = CASE Locked 1049 WHEN 0 THEN @now 1050 ELSE LockDate 1051 END, 1052 LockDateLocal = CASE Locked 1053 WHEN 0 THEN @nowLocal 1054 ELSE LockDateLocal 1055 END, 1056 @lockAge = CASE Locked 1057 WHEN 0 THEN 0 1058 ELSE DATEDIFF(second, LockDate, @now) 1059 END, 1060 @lockCookie = LockCookie = CASE Locked 1061 WHEN 0 THEN LockCookie + 1 1062 ELSE LockCookie 1063 END, 1064 @itemShort = CASE Locked 1065 WHEN 0 THEN SessionItemShort 1066 ELSE NULL 1067 END, 1068 @textptr = CASE Locked 1069 WHEN 0 THEN SessionItemLong 1070 ELSE NULL 1071 END, 1072 @length = CASE Locked 1073 WHEN 0 THEN DATALENGTH(SessionItemLong) 1074 ELSE NULL 1075 END, 1076 @locked = Locked, 1077 Locked = 1 1078 WHERE SessionId = @id 1079 IF @length IS NOT NULL BEGIN 1080 SELECT @textptr 1081 END 1082 COMMIT TRANSACTION; 1083 SET @retry = 0; -- //Stops the loop. 1084 END TRY 1085 1086 BEGIN CATCH 1087 SET @retry -= 1; 1088 1089 IF (@retry > 0 AND 1090 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1091 ) 1092 BEGIN 1093 IF XACT_STATE() = -1 1094 ROLLBACK TRANSACTION; 1095 1096 WAITFOR DELAY '00:00:00.001'; 1097 END 1098 ELSE 1099 BEGIN 1100 PRINT 'Suffered an error for which Retry is inappropriate.'; 1101 THROW; 1102 END 1103 END CATCH 1104 1105 END -- //While loop 1106 RETURN 0 1107GO 1108 1109CREATE PROCEDURE [dbo].[TempGetVersion] 1110 @ver char(10) OUTPUT 1111AS 1112 SELECT @ver = '2' 1113 RETURN 0 1114GO 1115 1116CREATE PROCEDURE [dbo].[TempInsertStateItemLong] 1117 @id nvarchar(88), 1118 @itemLong image, 1119 @timeout int 1120AS 1121 DECLARE @now AS datetime 1122 DECLARE @nowLocal AS datetime 1123 1124 SET @now = GETUTCDATE() 1125 SET @nowLocal = GETDATE() 1126 1127 DECLARE @retry INT = 10; 1128 1129 WHILE (@retry > 0) 1130 BEGIN 1131 BEGIN TRY 1132 BEGIN TRANSACTION; 1133 INSERT ASPStateTempSessions 1134 (SessionId, 1135 SessionItemLong, 1136 Timeout, 1137 Expires, 1138 Locked, 1139 LockDate, 1140 LockDateLocal, 1141 LockCookie) 1142 VALUES 1143 (@id, 1144 @itemLong, 1145 @timeout, 1146 DATEADD(n, @timeout, @now), 1147 0, 1148 @now, 1149 @nowLocal, 1150 1) 1151 COMMIT TRANSACTION; 1152 SET @retry = 0; -- //Stops the loop. 1153 END TRY 1154 1155 BEGIN CATCH 1156 SET @retry -= 1; 1157 1158 IF (@retry > 0 AND 1159 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1160 ) 1161 BEGIN 1162 IF XACT_STATE() = -1 1163 ROLLBACK TRANSACTION; 1164 1165 WAITFOR DELAY '00:00:00.001'; 1166 END 1167 ELSE 1168 BEGIN 1169 PRINT 'Suffered an error for which Retry is inappropriate.'; 1170 THROW; 1171 END 1172 END CATCH 1173 1174 END -- //While loop 1175 RETURN 0 1176GO 1177 1178CREATE PROCEDURE [dbo].[TempInsertUninitializedItem] 1179 @id nvarchar(88), 1180 @itemShort varbinary(7000), 1181 @timeout int 1182AS 1183 1184 DECLARE @now AS datetime 1185 DECLARE @nowLocal AS datetime 1186 1187 SET @now = GETUTCDATE() 1188 SET @nowLocal = GETDATE() 1189 1190 DECLARE @retry INT = 10; 1191 1192 WHILE (@retry > 0) 1193 BEGIN 1194 BEGIN TRY 1195 BEGIN TRANSACTION; 1196 INSERT ASPStateTempSessions 1197 (SessionId, 1198 SessionItemShort, 1199 Timeout, 1200 Expires, 1201 Locked, 1202 LockDate, 1203 LockDateLocal, 1204 LockCookie, 1205 Flags) 1206 VALUES 1207 (@id, 1208 @itemShort, 1209 @timeout, 1210 DATEADD(n, @timeout, @now), 1211 0, 1212 @now, 1213 @nowLocal, 1214 1, 1215 1) 1216 COMMIT TRANSACTION; 1217 SET @retry = 0; -- //Stops the loop. 1218 END TRY 1219 1220 BEGIN CATCH 1221 SET @retry -= 1; 1222 1223 IF (@retry > 0 AND 1224 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1225 ) 1226 BEGIN 1227 IF XACT_STATE() = -1 1228 ROLLBACK TRANSACTION; 1229 1230 WAITFOR DELAY '00:00:00.001'; 1231 END 1232 ELSE 1233 BEGIN 1234 PRINT 'Suffered an error for which Retry is inappropriate.'; 1235 THROW; 1236 END 1237 END CATCH 1238 1239 END -- //While loop 1240 RETURN 0 1241GO 1242 1243CREATE PROCEDURE [dbo].[TempReleaseStateItemExclusive] 1244 @id nvarchar(88), 1245 @lockCookie int 1246AS 1247DECLARE @retry INT = 10; 1248 1249 WHILE (@retry > 0) 1250 BEGIN 1251 BEGIN TRY 1252 BEGIN TRANSACTION; 1253 UPDATE ASPStateTempSessions 1254 SET Expires = DATEADD(n, Timeout, GETUTCDATE()), 1255 Locked = 0 1256 WHERE SessionId = @id AND LockCookie = @lockCookie 1257 COMMIT TRANSACTION; 1258 SET @retry = 0; -- //Stops the loop. 1259 END TRY 1260 1261 BEGIN CATCH 1262 SET @retry -= 1; 1263 1264 IF (@retry > 0 AND 1265 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1266 ) 1267 BEGIN 1268 IF XACT_STATE() = -1 1269 ROLLBACK TRANSACTION; 1270 1271 WAITFOR DELAY '00:00:00.001'; 1272 END 1273 ELSE 1274 BEGIN 1275 PRINT 'Suffered an error for which Retry is inappropriate.'; 1276 THROW; 1277 END 1278 END CATCH 1279 1280 END -- //While loop 1281 1282 RETURN 0 1283GO 1284 1285CREATE PROCEDURE [dbo].[TempRemoveStateItem] 1286 @id nvarchar(88), 1287 @lockCookie int 1288AS 1289 1290DECLARE @retry INT = 10; 1291 1292 WHILE (@retry > 0) 1293 BEGIN 1294 BEGIN TRY 1295 BEGIN TRANSACTION; 1296 DELETE ASPStateTempSessions 1297 WHERE SessionId = @id AND LockCookie = @lockCookie 1298 COMMIT TRANSACTION; 1299 SET @retry = 0; -- //Stops the loop. 1300 END TRY 1301 1302 BEGIN CATCH 1303 SET @retry -= 1; 1304 1305 IF (@retry > 0 AND 1306 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1307 ) 1308 BEGIN 1309 IF XACT_STATE() = -1 1310 ROLLBACK TRANSACTION; 1311 1312 WAITFOR DELAY '00:00:00.001'; 1313 END 1314 ELSE 1315 BEGIN 1316 PRINT 'Suffered an error for which Retry is inappropriate.'; 1317 THROW; 1318 END 1319 END CATCH 1320 1321 END -- //While loop 1322 RETURN 0 1323GO 1324 1325CREATE PROCEDURE [dbo].[TempResetTimeout] 1326 @id nvarchar(88) 1327AS 1328DECLARE @retry INT = 10; 1329 1330 WHILE (@retry > 0) 1331 BEGIN 1332 BEGIN TRY 1333 BEGIN TRANSACTION; 1334 UPDATE ASPStateTempSessions 1335 SET Expires = DATEADD(n, Timeout, GETUTCDATE()) 1336 WHERE SessionId = @id 1337 COMMIT TRANSACTION; 1338 SET @retry = 0; -- //Stops the loop. 1339 END TRY 1340 1341 BEGIN CATCH 1342 SET @retry -= 1; 1343 1344 IF (@retry > 0 AND 1345 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1346 ) 1347 BEGIN 1348 IF XACT_STATE() = -1 1349 ROLLBACK TRANSACTION; 1350 1351 WAITFOR DELAY '00:00:00.001'; 1352 END 1353 ELSE 1354 BEGIN 1355 PRINT 'Suffered an error for which Retry is inappropriate.'; 1356 THROW; 1357 END 1358 END CATCH 1359 1360 END -- //While loop 1361 1362 RETURN 0 1363GO 1364 1365CREATE PROCEDURE [dbo].[TempUpdateStateItemShortNullLong] 1366 @id nvarchar(88), 1367 @itemShort varbinary(7000), 1368 @timeout int, 1369 @lockCookie int 1370AS 1371DECLARE @retry INT = 10; 1372 1373 WHILE (@retry > 0) 1374 BEGIN 1375 BEGIN TRY 1376 BEGIN TRANSACTION; 1377 UPDATE ASPStateTempSessions 1378 SET Expires = DATEADD(n, @timeout, GETUTCDATE()), 1379 SessionItemShort = @itemShort, 1380 SessionItemLong = NULL, 1381 Timeout = @timeout, 1382 Locked = 0 1383 WHERE SessionId = @id AND LockCookie = @lockCookie 1384 COMMIT TRANSACTION; 1385 SET @retry = 0; -- //Stops the loop. 1386 END TRY 1387 1388 BEGIN CATCH 1389 SET @retry -= 1; 1390 1391 IF (@retry > 0 AND 1392 ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41839, 1205) 1393 ) 1394 BEGIN 1395 IF XACT_STATE() = -1 1396 ROLLBACK TRANSACTION; 1397 1398 WAITFOR DELAY '00:00:00.001'; 1399 END 1400 ELSE 1401 BEGIN 1402 PRINT 'Suffered an error for which Retry is inappropriate.'; 1403 THROW; 1404 END 1405 END CATCH 1406 1407 END -- //While loop 1408 1409 RETURN 0 1410GO 1411/*http://example.com.*/ 1412/* comment /* comment */ 1413comment 1414*/ 1415