/Kilimanjaro_Trunk/DialogPool/Scripts/2 - Dialog pool setup.sql
SQL | 173 lines | 109 code | 21 blank | 43 comment | 0 complexity | 2c1f75fd46ad2f5ee8067e780f461cd0 MD5 | raw file
1-------------------------------------------------------------------- 2-- Script for dialog pool sample. 3-- 4-- This file is part of the Microsoft SQL Server Code Samples. 5-- Copyright (C) Microsoft Corporation. All Rights reserved. 6-- This source code is intended only as a supplement to Microsoft 7-- Development Tools and/or on-line documentation. See these other 8-- materials for detailed information regarding Microsoft code samples. 9-- 10-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 11-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 12-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A 13-- PARTICULAR PURPOSE. 14-------------------------------------------------------------------- 15 16-------------------------------------------------------------------------- 17-- Dialog pool setup. 18-------------------------------------------------------------------------- 19 20USE SsbDemoDb; 21GO 22 23-------------------------------------------------------------------------- 24-- The dialog pool table. 25-- Obtain a conversation handle using from service, to service, and contract. 26-- Also indicates age and usage of dialog for auditing purposes. 27-------------------------------------------------------------------------- 28 29IF EXISTS (SELECT name FROM sys.tables WHERE name = 'DialogPool') 30 DROP TABLE DialogPool; 31GO 32 33CREATE TABLE DialogPool ( 34 FromService SYSNAME NOT NULL, 35 ToService SYSNAME NOT NULL, 36 OnContract SYSNAME NOT NULL, 37 Handle UNIQUEIDENTIFIER NOT NULL, 38 OwnerSPID INT NOT NULL, 39 CreationTime DATETIME NOT NULL, 40 SendCount BIGINT NOT NULL, 41 UNIQUE (Handle)); 42GO 43 44-------------------------------------------------------------------------- 45-- Get dialog procedure. 46-- Reuse a free dialog in the pool or create a new one in case 47-- no free dialogs exist. 48-- Input is from service, to service, and contract. 49-- Output is dialog handle and count of message previously sent on dialog. 50-------------------------------------------------------------------------- 51 52IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_get_dialog') 53 DROP PROC usp_get_dialog; 54GO 55 56CREATE PROCEDURE usp_get_dialog ( 57 @fromService SYSNAME, 58 @toService SYSNAME, 59 @onContract SYSNAME, 60 @dialogHandle UNIQUEIDENTIFIER OUTPUT, 61 @sendCount BIGINT OUTPUT) 62AS 63BEGIN 64 SET NOCOUNT ON; 65 66 DECLARE @dialog TABLE 67 ( 68 FromService SYSNAME NOT NULL, 69 ToService SYSNAME NOT NULL, 70 OnContract SYSNAME NOT NULL, 71 Handle UNIQUEIDENTIFIER NOT NULL, 72 OwnerSPID INT NOT NULL, 73 CreationTime DATETIME NOT NULL, 74 SendCount BIGINT NOT NULL 75 ); 76 77 -- Try to claim an unused dialog in DialogPool 78 -- READPAST option avoids blocking on locked dialogs. 79 BEGIN TRANSACTION; 80 DELETE @dialog; 81 UPDATE TOP(1) DialogPool WITH(READPAST) 82 SET OwnerSPID = @@SPID 83 OUTPUT INSERTED.* INTO @dialog 84 WHERE FromService = @fromService 85 AND ToService = @toService 86 AND OnContract = @OnContract 87 AND OwnerSPID = -1; 88 IF @@ROWCOUNT > 0 89 BEGIN 90 SET @dialogHandle = (SELECT Handle FROM @dialog); 91 SET @sendCount = (SELECT SendCount FROM @dialog); 92 END 93 ELSE 94 BEGIN 95 -- No free dialogs: need to create a new one 96 BEGIN DIALOG CONVERSATION @dialogHandle 97 FROM SERVICE @fromService 98 TO SERVICE @toService 99 ON CONTRACT @onContract 100 WITH ENCRYPTION = OFF; 101 102 INSERT INTO DialogPool 103 (FromService, ToService, OnContract, Handle, OwnerSPID, 104 CreationTime, SendCount) 105 VALUES 106 (@fromService, @toService, @onContract, @dialogHandle, @@SPID, 107 GETDATE(), 0); 108 SET @sendCount = 0; 109 END 110 COMMIT; 111END; 112GO 113 114-------------------------------------------------------------------------- 115-- Free dialog procedure. 116-- Return the dialog to the pool. 117-- Inputs are dialog handle and updated send count. 118-------------------------------------------------------------------------- 119 120IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_free_dialog') 121 DROP PROC usp_free_dialog; 122GO 123 124CREATE PROCEDURE usp_free_dialog ( 125 @dialogHandle UNIQUEIDENTIFIER, 126 @sendCount BIGINT) 127AS 128BEGIN 129 SET NOCOUNT ON; 130 131 DECLARE @rowcount INT; 132 DECLARE @string VARCHAR(50); 133 134 BEGIN TRANSACTION; 135 136 -- Release dialog by setting OwnerSPID to -1. 137 UPDATE [DialogPool] SET OwnerSPID = -1, SendCount = @sendCount WHERE Handle = @dialogHandle; 138 SELECT @rowcount = @@ROWCOUNT; 139 IF @rowcount = 0 140 BEGIN 141 SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50))); 142 RAISERROR('usp_free_dialog: dialog %s not found in dialog pool', 16, 1, @string) WITH LOG; 143 END 144 ELSE IF @rowcount > 1 145 BEGIN 146 SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50))); 147 RAISERROR('usp_free_dialog: duplicate dialog %s found in dialog pool', 16, 1, @string) WITH LOG; 148 END 149 150 COMMIT; 151END; 152GO 153 154-------------------------------------------------------------------------- 155-- Delete dialog procedure. 156-- Delete the dialog from the pool. This does not end the dialog. 157-- Input is dialog handle. 158-------------------------------------------------------------------------- 159IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_delete_dialog') 160 DROP PROC usp_delete_dialog; 161GO 162 163CREATE PROCEDURE usp_delete_dialog ( 164 @dialogHandle UNIQUEIDENTIFIER) 165AS 166BEGIN 167 SET NOCOUNT ON; 168 169 BEGIN TRANSACTION; 170 DELETE [DialogPool] WHERE Handle = @dialogHandle; 171 COMMIT; 172END; 173GO