I don't know if this question duplicate. Please give me the link of it if this question is duplicated.
My question is how to call 2 stored procedures that consists of BEGIN TRANSACTION & COMMIT TRANSACTION (Service Broker).
I have 2 stored procedures that use to do some action of Service Broker.
This is the stored procedure that contains BEGIN CONVERSATION:
USE [EventCloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SendingMessage_Group_Id]
@reference_id UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @conversation_group_id UNIQUEIDENTIFIER
DECLARE @msg NVARCHAR(MAX)
SET @conversation_group_id = @reference_id
BEGIN TRY
BEGIN TRANSACTION
BEGIN DIALOG CONVERSATION @ch
FROM SERVICE [InitiatorService]
TO SERVICE 'TargetService'
ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
WITH RELATED_CONVERSATION_GROUP = @conversation_group_id,
ENCRYPTION = OFF
SET @msg = '<HelloWorldRequest>1234</HelloWorldRequest>'
;SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
(
@msg
)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
Code below is an internal activation stored procedure of TargetQueue:
USE [EventCloud]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcessRequestMessage]
AS
BEGIN
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML
DECLARE @responsemessage XML;
DECLARE @errorcode INT
DECLARE @errormessage NVARCHAR(3000);
WHILE (1 = 1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
WAITFOR(
RECEIVE TOP (1)
@ch = conversation_handle,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM TargetQueue
)
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
-- Process the requested message and send back to Initiator
ELSE IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
BEGIN
-- Store the received request message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName, ProcessedDateTime)
VALUES (NEWID(), @messagebody, 'TargetService', GETDATE())
-- Construct the response message
SET @responsemessage =
'<HelloWorldResponse>' +
@messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') +
'</HelloWorldResponse>';
-- Send the response message back to the initiating service
SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
(
@responsemessage
);
-- END the conversation on the target's side
END CONVERSATION @ch;
END
-- End the conversation if meet the message type
IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
-- End the conversation
END CONVERSATION @ch;
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH
END
END
And this code is used to receive the response message from InitiatorQueue:
ALTER PROCEDURE [dbo].[ProcessMessageWithTimeOut]
@reference_id UNIQUEIDENTIFIER,
@receive_timeout INT
AS
BEGIN
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @conversation_group_id UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML
DECLARE @responsemessage XML
DECLARE @errorcode INT
DECLARE @errormessage NVARCHAR(3000)
DECLARE @queuing_order BIGINT
DECLARE @timeout INT
SET @conversation_group_id = @reference_id
SET @timeout = @receive_timeout
DECLARE @tableMessage TABLE
(
queuing_order BIGINT,
conversation_handle UNIQUEIDENTIFIER,
message_type_name NVARCHAR(256),
message_body VARBINARY(MAX)
)
BEGIN TRY
BEGIN TRANSACTION
WAITFOR(
RECEIVE
queuing_order,
conversation_handle,
message_type_name,
message_body
FROM InitiatorQueue INTO @tableMessage
WHERE conversation_group_id = @conversation_group_id
), TIMEOUT @timeout;
DECLARE @count INT
SET @count = (SELECT COUNT(*) FROM @tableMessage)
IF (@count = 0)
BEGIN;
THROW 50001, 'No message response within 5 seconds.', 1
END
IF (@count <>2)
BEGIN
DECLARE @timeout2 INT
SET @timeout2 = ABS(@timeout * 0.5)
WAITFOR(
RECEIVE
queuing_order,
conversation_handle,
message_type_name,
message_body
FROM InitiatorQueue INTO @tableMessage
WHERE conversation_group_id = @conversation_group_id
), TIMEOUT 5000
SET @count = (SELECT COUNT(*) FROM @tableMessage)
IF (@count <> 2)
BEGIN;
THROW 50002, 'End Dialog without Response Message', 1
END
END
WHILE (@count <> 0)
BEGIN
SET @queuing_order = (SELECT TOP 1 queuing_order FROM @tableMessage)
SET @ch = (SELECT conversation_handle FROM @tableMessage WHERE queuing_order = @queuing_order)
SET @messagetypename = (SELECT message_type_name FROM @tableMessage WHERE queuing_order = @queuing_order)
SET @messagebody = CAST((SELECT message_body FROM @tableMessage WHERE queuing_order = @queuing_order) AS XML)
IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
BEGIN
-- Store the received response message in a table
INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName, ProcessedDateTime)
VALUES (NEWID(), @messagebody, 'InitiatorService', GETDATE())
END
ELSE IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
-- End the conversation on the initiator's side
END CONVERSATION @ch
END
DELETE FROM @tableMessage WHERE queuing_order = @queuing_order
SET @count = (SELECT COUNT(*) FROM @tableMessage)
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 50001
BEGIN
;THROW
END
IF ERROR_NUMBER() = 50002
BEGIN
;THROW
END
;THROW
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH
END
This is my C# code that is used to call the stored procedure:
public async Task<TestObject> Begin_Conversation_With_Group_Id(Guid ch)
{
try
{
return await Context.Database.SqlQuery<TestObject>(
"EXEC SendingMessage_Group_Id @ch",
new SqlParameter("ch", ch))
.SingleOrDefaultAsync();
}
catch (Exception e)
{
TestObject exception = new TestObject();
exception.Data = "Cannot get the data due to: " + " " + e.Message;
return exception;
}
}
public async Task<TestObject> Process_Response_Message_With_TimeOut(Guid ch)
{
var timeout = 5000;
try
{
return await Context.Database.SqlQuery<TestObject>(
"EXEC ProcessMessageWithTimeOut @ch, @timeout",
new SqlParameter("ch", ch),
new SqlParameter("timeout", timeout))
.SingleOrDefaultAsync();
}
catch (SqlException ex)
{
TestObject exception = new TestObject();
exception.Data = "Process is not finish yet due to: " + ex.Message + " " + ex.Number;
return exception;
}
}
The problem is when I run the code, the message is still in TargetQueue, by right it should be received and processed by the internal activation.
But it's working fine if only call 1 stored procedure (SendingMessage_Group_Id). The message is able to response back to InitiatorQueue instead of staying in TargetQueue.
From my guessing, is because of the transaction is not commit yet.
The issue has been solved.
The reason of this issue happened is because I use a framework that cause this situation happened. The framework is included a transaction at starting, so what I need to do is disable the transaction of the framework then the issue is solved.
The framework that I'm using is ASP.NET Zero