Hi Nils,
thank you for your prompt reply!
Unfortunately, there's no transaction involved (neither in the SEND nor the RECEIVE block).
The concept is based on Rusanu's reusing-conversations script (link in the SQL code).
ALTER PROCEDURE [dbo].[myConversation]
(
@fromService SYSNAME,
@toService SYSNAME,
@onContract SYSNAME,
@messageType SYSNAME,
@message VARCHAR(MAX)
)
AS
BEGIN
-- source: http://rusanu.com/2007/04/25/reusing-conversations/
SET NOCOUNT ON;
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @counter INT;
DECLARE @error INT;
SELECT @counter = 1;
BEGIN TRY
-- Will need a loop to retry in case the conversation is
-- in a state that does not allow transmission
--
WHILE (1=1)
BEGIN
-- Seek an eligible conversation in [SessionConversations]
--
SELECT @handle = Handle
FROM [SessionConversations]
WHERE SPID = @@SPID
AND FromService = @fromService
AND ToService = @toService
AND OnContract = @OnContract;
IF @handle IS NULL
BEGIN
-- Need to start a new conversation for the current @@spid
--
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @fromService
TO SERVICE @toService
ON CONTRACT @onContract
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@handle) TIMEOUT = 43200;
INSERT INTO [SessionConversations]
(SPID, FromService, ToService, OnContract, Handle)
VALUES
(@@SPID, @fromService, @toService, @onContract, @handle);
END;
-- Attempt to SEND on the associated conversation
--
SEND ON CONVERSATION @handle
MESSAGE TYPE @messageType
(@message);
SELECT @error = @@ERROR;
IF @error = 0
BEGIN
-- Successful send, just exit the loop
--
BREAK;
END
SELECT @counter = @counter+1;
IF @counter > 10
BEGIN
-- We failed 10 times in a row, something must be broken
--
INSERT INTO Error_Log(
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9)
SELECT
getdate(),
'myProc',
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
'SEND on a conversation failed10x.' + @message +','+@@ERROR
BREAK;
END
-- Delete the associated conversation from the table and try again
--
DELETE FROM [SessionConversations]
WHERE Handle = @handle;
SELECT @handle = NULL;
END
END TRY
BEGIN CATCH
INSERT INTO Error_Log(
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9)
SELECT
getdate(),
'myProc',
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
'SEND on a conversation failed. ' + @message +','+@@ERROR
END CATCH
END
I'm confident the SEND and RECEIVE prcedures do not contain any ROLLBACK command. But it might be the sprocs being called based on the RECEIVE command can raise a ROLLBACK. But since neither the SEND nor the RECEIVE block are wrapped in a transaction, this shouldn't affect the Service Broker handling. At least not from my point of view... 😉