239 lines
6.8 KiB
Transact-SQL
239 lines
6.8 KiB
Transact-SQL
IF OBJECT_ID('auditlog.DialogPool') IS NOT NULL
|
|
DROP TABLE [auditlog].[DialogPool];
|
|
GO
|
|
|
|
CREATE TABLE [auditlog].[DialogPool] (
|
|
[FromService] SYSNAME NOT NULL,
|
|
[ToService] SYSNAME NOT NULL,
|
|
[OnContract] SYSNAME NOT NULL,
|
|
[Handle] UNIQUEIDENTIFIER NOT NULL,
|
|
[OwnerSPID] INT NOT NULL,
|
|
[CreationTime] DATETIME NOT NULL,
|
|
[SendCount] BIGINT NOT NULL,
|
|
CONSTRAINT [UQ_DialogPool_Handle] UNIQUE (Handle)
|
|
);
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('auditlog.usp_get_dialog') IS NOT NULL
|
|
DROP PROCEDURE [auditlog].[usp_get_dialog];
|
|
GO
|
|
|
|
CREATE PROCEDURE [auditlog].[usp_get_dialog]
|
|
@fromService SYSNAME,
|
|
@toService SYSNAME,
|
|
@onContract SYSNAME,
|
|
@dialogHandle UNIQUEIDENTIFIER OUTPUT,
|
|
@sendCount BIGINT OUTPUT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @dialog TABLE (
|
|
[FromService] SYSNAME NOT NULL,
|
|
[ToService] SYSNAME NOT NULL,
|
|
[OnContract] SYSNAME NOT NULL,
|
|
[Handle] UNIQUEIDENTIFIER NOT NULL,
|
|
[OwnerSPID] INT NOT NULL,
|
|
[CreationTime] DATETIME NOT NULL,
|
|
[SendCount] BIGINT NOT NULL
|
|
);
|
|
|
|
-- Try to claim an unused dialog in DialogPool
|
|
-- READPAST option avoids blocking on locked dialogs.
|
|
BEGIN TRANSACTION;
|
|
DELETE FROM @dialog;
|
|
UPDATE TOP(1) [auditlog].[DialogPool] WITH(READPAST)
|
|
SET [OwnerSPID] = @@SPID
|
|
OUTPUT INSERTED.* INTO @dialog
|
|
WHERE [FromService] = @fromService
|
|
AND [ToService] = @toService
|
|
AND [OnContract] = @OnContract
|
|
AND [OwnerSPID] = -1;
|
|
|
|
IF @@ROWCOUNT > 0 BEGIN
|
|
SET @dialogHandle = (SELECT [Handle] FROM @dialog);
|
|
SET @sendCount = (SELECT [SendCount] FROM @dialog);
|
|
END
|
|
ELSE BEGIN
|
|
-- No free dialogs: need to create a new one
|
|
BEGIN DIALOG CONVERSATION @dialogHandle
|
|
FROM SERVICE @fromService
|
|
TO SERVICE @toService
|
|
ON CONTRACT @onContract
|
|
WITH ENCRYPTION = OFF;
|
|
|
|
INSERT INTO [auditlog].[DialogPool]
|
|
([FromService], [ToService], [OnContract], [Handle], [OwnerSPID], [CreationTime], [SendCount])
|
|
VALUES
|
|
(@fromService, @toService, @onContract, @dialogHandle, @@SPID, GETDATE(), 0);
|
|
|
|
SET @sendCount = 0;
|
|
END
|
|
|
|
COMMIT TRANSACTION;
|
|
END
|
|
GO
|
|
|
|
--------------------------------------------------------------------------
|
|
-- Free dialog procedure.
|
|
-- Return the dialog to the pool.
|
|
-- Inputs are dialog handle and updated send count.
|
|
--------------------------------------------------------------------------
|
|
|
|
IF OBJECT_ID('auditlog.usp_free_dialog') IS NOT NULL
|
|
DROP PROCEDURE [auditlog].[usp_free_dialog];
|
|
GO
|
|
|
|
CREATE PROCEDURE [auditlog].[usp_free_dialog]
|
|
@dialogHandle UNIQUEIDENTIFIER,
|
|
@sendCount BIGINT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @rowcount INT;
|
|
DECLARE @string VARCHAR(50);
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
-- Release dialog by setting OwnerSPID to -1.
|
|
UPDATE [auditlog].[DialogPool] SET [OwnerSPID] = -1, [SendCount] = @sendCount
|
|
WHERE [Handle] = @dialogHandle;
|
|
|
|
SET @rowcount = @@ROWCOUNT;
|
|
|
|
IF @rowcount = 0 BEGIN
|
|
SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));
|
|
RAISERROR('usp_free_dialog: dialog %s not found in dialog pool', 16, 1, @string) WITH LOG;
|
|
END
|
|
ELSE IF @rowcount > 1 BEGIN
|
|
SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));
|
|
RAISERROR('usp_free_dialog: duplicate dialog %s found in dialog pool', 16, 1, @string) WITH LOG;
|
|
END
|
|
COMMIT TRANSACTION;
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID('auditlog.usp_delete_dialog') IS NOT NULL
|
|
DROP PROCEDURE [auditlog].[usp_delete_dialog];
|
|
GO
|
|
|
|
CREATE PROCEDURE [auditlog].[usp_delete_dialog]
|
|
@dialogHandle UNIQUEIDENTIFIER
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
BEGIN TRY
|
|
BEGIN TRANSACTION;
|
|
DELETE [DialogPool] WHERE Handle = @dialogHandle;
|
|
COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
-- Rollback any active or uncommittable transactions before
|
|
-- inserting information in the ErrorLog
|
|
IF @@TRANCOUNT > 0 BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
END
|
|
|
|
EXECUTE [auditlog].[uspLogError];
|
|
END CATCH;
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID('auditlog.usp_send') IS NOT NULL
|
|
DROP PROCEDURE [auditlog].[usp_send];
|
|
GO
|
|
|
|
CREATE PROCEDURE [auditlog].[usp_send]
|
|
@fromService SYSNAME,
|
|
@toService SYSNAME,
|
|
@onContract SYSNAME,
|
|
@messageType SYSNAME,
|
|
@messageBody NVARCHAR(MAX)
|
|
WITH EXECUTE AS 'Kreta_tech_user'
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @dialogHandle UNIQUEIDENTIFIER;
|
|
DECLARE @sendCount BIGINT;
|
|
DECLARE @counter INT;
|
|
DECLARE @error INT;
|
|
|
|
|
|
SET @counter = 1;
|
|
BEGIN TRANSACTION;
|
|
|
|
-- message validation check, preventing poison message at the target service validation
|
|
-- target service validates WELL_FORMED_XML types, so it is best to prevent it at here
|
|
|
|
IF EXISTS (SELECT 1 FROM sys.[service_message_types] WHERE [name] = @messageType AND [validation] = 'X')
|
|
AND (TRY_CONVERT(xml, @messageBody) IS NOT NULL)
|
|
BEGIN
|
|
SET @messageBody = CAST(TRY_CONVERT(xml, @messageBody) AS nvarchar(max))
|
|
END
|
|
ELSE BEGIN
|
|
INSERT INTO [auditlog].[AuditServiceFailedItems] ([conversation_handle], [message_body])
|
|
VALUES ('00000000-0000-0000-0000-000000000000', @messageBody)
|
|
COMMIT TRANSACTION;
|
|
RAISERROR('XML validation failure before sending. Check [auditlog].[AuditServiceFailedItems] for failed message.', 16,1) WITH LOG;
|
|
END
|
|
|
|
-- Will need a loop to retry in case the dialog is
|
|
-- in a state that does not allow transmission
|
|
WHILE (1=1) BEGIN
|
|
-- Claim a dialog from the dialog pool.
|
|
-- A new one will be created if none are available.
|
|
EXEC [auditlog].[usp_get_dialog] @fromService, @toService, @onContract, @dialogHandle OUTPUT, @sendCount OUTPUT;
|
|
|
|
-- Attempt to SEND on the dialog
|
|
IF (@messageBody IS NOT NULL) BEGIN
|
|
-- If the @messageBody is not null it must be sent explicitly
|
|
|
|
SEND ON CONVERSATION @dialogHandle MESSAGE TYPE @messageType (@messageBody);
|
|
END
|
|
ELSE BEGIN
|
|
-- Messages with no body must *not* specify the body,
|
|
-- cannot send a NULL value argument
|
|
SEND ON CONVERSATION @dialogHandle MESSAGE TYPE @messageType;
|
|
END
|
|
|
|
SET @error = @@ERROR;
|
|
IF @error = 0 BEGIN
|
|
-- Successful send, increment count and exit the loop
|
|
SET @sendCount = @sendCount + 1;
|
|
BREAK;
|
|
END
|
|
|
|
SET @counter = @counter + 1;
|
|
IF @counter > 10 BEGIN
|
|
-- We failed 10 times in a row, something must be broken
|
|
RAISERROR('Failed to SEND on a conversation for more than 10 times. Error %i.', 16, 1, @error) WITH LOG;
|
|
BREAK;
|
|
END
|
|
|
|
-- Delete the associated dialog from the table and try again
|
|
EXEC [auditlog].[usp_delete_dialog] @dialogHandle;
|
|
SELECT @dialogHandle = NULL;
|
|
END
|
|
|
|
-- "Criterion" for dialog pool removal is send count > 1000.
|
|
-- Modify to suit application.
|
|
-- When deleting also inform the target to end the dialog.
|
|
IF @sendCount > 1000 BEGIN
|
|
EXEC [auditlog].[usp_delete_dialog] @dialogHandle ;
|
|
SEND ON CONVERSATION @dialogHandle MESSAGE TYPE [EndOfStream];
|
|
END
|
|
ELSE BEGIN
|
|
-- Free the dialog.
|
|
EXEC [auditlog].[usp_free_dialog] @dialogHandle, @sendCount;
|
|
END
|
|
|
|
COMMIT TRANSACTION;
|
|
END
|
|
GO
|
|
|
|
|