97 lines
3.1 KiB
Transact-SQL
97 lines
3.1 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS [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 ((TRY_CONVERT(xml, @messageBody) IS NOT NULL OR ISJSON(@messageBody) = 1))
|
|
BEGIN
|
|
IF ISJSON(@messageBody) = 0 BEGIN
|
|
SET @messageBody = CAST(TRY_CONVERT(xml, @messageBody) AS nvarchar(max))
|
|
END
|
|
END
|
|
ELSE BEGIN
|
|
INSERT INTO [auditlog].[AuditServiceFailedItems] ([conversation_handle], [message_body])
|
|
VALUES ('00000000-0000-0000-0000-000000000000', @messageBody)
|
|
COMMIT TRANSACTION;
|
|
RAISERROR('XML or JSON 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
|
|
|
|
|