I have a pair of services.
One is an initiator of a conversation, another is a target. I have a pair of message types, one sent by the initiator one sent by the target.
I have a pair of queues corresponding to the above services. Both queues have activation stored procedures associate with them.
The intiiator sends its mesage to the queue that the target service is defined on.
The activation procedure successfully retrieves the message, does some work and then sends a reply message on the same conversation to the initiating service.
At this point behavior seems to become strange.
Some of the time the send from the target service produces the "zero length message" error and indeed at times the message body is empty - while at others (no code changes just another trial send) it is not.
All of the time the RECEIVE on the Initiator's queue executed by the Activated Procedure (in response to the target sending a reply) gives a @.@.ROWCOUNT = 0 (i.e. even when there are no errors generated)
However in this case, the message_body field contains the (target sent, reply) message
The Target RECEIVE and its SEND reply are both executed within the same transaction and the SEND is performed by a stored procedure call passing the conversation handle (inter alia) as a parameter.
The Initiator's queue (with RETENTION = ON) then shows 2 messages in it, one is the reply message (with a status of 0) and the other is the original message (sent by the initiator with status of 3)
All services and queues and Activation procedures are in the same database. Dialog encryption is off.
The intial SEND is done by a simple driver stored procedure in the same database
The security context of the Activation procedures for the 2 queues is - as far as I can tell - the same.
Objects were all created in the dbo security context.
The "work" involves updates to tables in the local database.
No doubt, I have done something stupid somewhere and am incapable of recognising it, but (apart from the work performed by the target procedure) what I have done appears to be no more than the above, using Service Broker SQL copied from examples, with my identifiers substituted for queue names, etc.
Any thoughts anyone?
rrkk wrote:
Some of the time the send from the target service produces the "zero length message" error and indeed at times the message body is empty - while at others (no code changes just another trial send) it is not.
I'm not familiar with the "zero length message" error. Can you post the actual SQL error message?
The Initiator's queue (with RETENTION = ON) then shows 2 messages in it, one is the reply message (with a status of 0) and the other is the original message (sent by the initiator with status of 3)
Status = 0 indicates a message that was RECEIVEd from the queue (was already returned in a RECEIVE resultset). In your send script, are you trying to RECEIVE the reply as well? This RECEIVE will conflict with the activated procedure's RECEIVE.
HTH,
~ Remus
Thanks for the reply.
The RECEIVE of the reply messsage takes place in a different stored procedure from the one that executes the SEND. It is however, of course, associated with the same service (as its Activation Procedure) that is used in the sending stored procedure's FROM SERVICE clause in the BEGIN DIALOG statement.
To my - uninitiated eye - it looks almost like a timing or threading issue. After all, all of this activity is taking place inside the same database: one stored proc sends a message (on one queue), another is activated to receive, it then sends a reply (on another queue) which causes another store procedure to be activated to consume the reply.
This of course, is a deliberately "simple" set up to facilitate learning of how Service Broker works. In a production environement there would be external components also.
The text of the message is:
The message body may not be NULL. A zero-length UNICODE or binary string is allowed
.... which from my reading is a designed behaviour in the case of a response (Target) message that is in fact empty.
My puzzle is that the appearance of this error seems intermittent. The only candidate cause for an empty messsage is a string concatenation operation on a variable that is populated from data RECEIVED. If this were to be NULL, etc, this would explain it.
However, the SEND/RECEIVE code does not change from trial to trial. Extra "PRINTs" are added, @.@.ROWCOUNT tests are replaced by "IF @.message_body IS NULL" (in the procedure that recieves the reply message) tests - but processing logic does not change at any of the endpoints.
|||
Just a shot in the dark, but are you RECEIVEing the message_body into a @.variable and then SENDing back a response of the same @.variable w/o actually checking the message_type_name received? My guess is that you are treating all message types the same way, incuding the EndDialog message, that is part of every dialog and has a NULL message body.
Posting the actual stored procs you have might help.
HTH,
~ Remus
Remus,
thank you very much for your replies. It will probably turn out just to be "noise" born of ignorance, so I very mcuh appreciate your help.
When I show up at work on Monday I will do so
Cheers,
Roger
|||Remus,
Here is the code that is running (sic!).
Please bear in mind that it is pretty much still in the experimental stage with no thought yet being given to optimisation or robustness, etc. - but simply to get a sound grasp of how things work.
Thanks
Roger
=====>>>
-- *********** Service Broker Objects
alter QUEUE dbo.SurveySetQueue WITH
STATUS = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ShredSurveySet,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'dbo'
)
ALTER QUEUE dbo.ParseRequestsQueue
WITH
STATUS = ON,
RETENTION = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = UpdateSetParseState,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'dbo'
)
CREATE MESSAGE TYPE SurveySetMessage VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE ParseOutcomeMessage VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT SurveySetContract (
SurveySetMessage SENT BY INITIATOR,
ParseOutcomeMessage SENT BY TARGET
);
CREATE SERVICE SurveySetService ON QUEUE SurveySetQueue (SurveySetContract);
GO
CREATE SERVICE ParseRequestsService ON QUEUE ParseRequestsQueue (SurveySetContract);
GO
-- ***********
-- ******** STORED PROCS
-- ************* Initial sending procedure:
ALTER PROCEDURE SendParseRequestMessage(@.SetId int)
AS
BEGIN
DECLARE @.message XML ;
BEGIN TRY
BEGIN TRANSACTION ;
UPDATE CustomerSetMap SET State = 1 WHERE SetId = @.SetId -- AND State = 0;
SET @.message = N'<SetId>' + CAST(@.SetId AS nvarchar(10)) + N'</SetId>';
-- Declare a variable to hold the conversation
-- handle.
DECLARE @.conversationHandle UNIQUEIDENTIFIER ;
-- Begin the dialog.
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE ParseRequestsService
TO SERVICE 'SurveySetService'
ON CONTRACT SurveySetContract
WITH ENCRYPTION = OFF;
-- Send the message on the dialog.
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE SurveySetMessage
(@.message) ;
COMMIT TRANSACTION ;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
exec LogError
END CATCH
END
- Procedeure activates as the result of the initial SEND
ALTER PROCEDURE ShredSurveySet
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.message_body XML,
@.message_type_name NVARCHAR(256),
@.dialog UNIQUEIDENTIFIER,
@.ErrorMessage nvarchar(max);
DECLARE @.SetId int,
@.SurveyXml xml,
@.SurveyId uniqueidentifier,
@.conversation_group_id uniqueidentifier,
@.CursorDefined bit;
-- One at a time for development purposes
BEGIN TRY
BEGIN TRANSACTION ;
SET @.CursorDefined = 0;
SET @.SurveyXml = null;
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@.message_type_name=message_type_name, --the type of message received
@.message_body=
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [dbo].SurveySetQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away
IF (@.@.ROWCOUNT = 0) --@.SurveyXml is null --(
BEGIN
print 'no rows'
print cast(@.dialog as nvarchar(50));
-- For development purposes do not try any
-- poison message handling due to consecutive rollbacks
COMMIT TRANSACTION ;
RETURN ;
END ;
-- Check to see if the message is an end dialog message.
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@.dialog as nvarchar(40)) ;
END CONVERSATION @.dialog ;
END ;
-- Error?
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
SET @.ErrorMessage = CAST(@.message_body as nvarchar(max));
RAISERROR( @.ErrorMessage, 1, 1);
RETURN;
END
-- Extract the data element information using XQuery.
SET @.SetId = @.message_body.value('(/SetId)[1]', 'int');
--select @.SetId;
-- ********* This is the DB work section
DECLARE Survey_Cursor Cursor LOCAL FOR SELECT top 5 SurveyId FROM [WSS Surveys] WHERE SetId = @.SetId ORDER BY Ordinal;
SET @.CursorDefined = 1;
OPEN Survey_Cursor;
FETCH NEXT FROM Survey_Cursor INTO @.SurveyId;
-- Shred XML into a table set
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC MakeXMLSurveyTempTables1 @.SurveyId;
FETCH NEXT FROM Survey_Cursor INTO @.SurveyId;
END
CLOSE Survey_Cursor;
DEALLOCATE Survey_Cursor;
SET @.CursorDefined = 0;
-- ******************
-- ******** The Reply
-- Send a message back to the originating service
-- indicating success
EXEC SendParseUpdateMessage @.dialog, @.SetId, 2;
COMMIT TRANSACTION ;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;
-- Send a message back to the originating service
-- indicating error
EXEC SendParseUpdateMessage @.dialog, @.SetId, 9;
-- Deaalocate cursor if necessary
IF @.CursorDefined = 1
DEALLOCATE Survey_Cursor;
-- Write to error table
EXEC LogError;
END CATCH;
--END;
END
-- Procedure that sends the reply
ALTER PROCEDURE dbo.SendParseUpdateMessage(@.conversationHandle UNIQUEIDENTIFIER, @.SetId int, @.State smallint)
AS
BEGIN
DECLARE @.message XML ;
BEGIN TRY
SET @.message = N'<UpdateState><SetId>' + CAST(@.SetId AS nvarchar(10)) + N'</SetId><State>' +
CAST(@.State AS nvarchar(4)) + N'</State></UpdateState>';
-- Send the message on the dialog.
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE ParseOutcomeMessage
(@.message) ;
-- print 'message sent' + cast(@.message as nvarchar(max));
END TRY
BEGIN CATCH
EXEC LogError
END CATCH
END
-- This is the Activation stored proc for processing the reply
ALTER PROCEDURE dbo.UpdateSetParseState
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.message_body XML,
@.message_type_name NVARCHAR(256),
@.dialog UNIQUEIDENTIFIER,
@.ErrorMessage nvarchar(max);
DECLARE @.SetId int,
@.State smallint;
-- One at a time for development purposes
BEGIN TRY
BEGIN TRANSACTION ;
SET @.message_body = null;
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@.message_type_name=message_type_name, --the type of message received
@.message_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM ParseRequestsQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away
-- ***** So far always 0
print 'rowcount = ' + cast(@.@.ROWCOUNT as nvarchar(10));
-- *******************************************
-- *******************************************
--
-- THIS IS WHERE I AM PUZZLED
--
-- *******************************************
-- *******************************************
--IF (@.@.ROWCOUNT = 0)
IF @.message_body IS NULL
BEGIN
-- NO poison handling due to repeated rollbacks
COMMIT TRANSACTION;
Print ' no rows: parse update'
RETURN;
END ;
-- Check to see if the message is an end dialog message.
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@.dialog as nvarchar(40)) ;
END CONVERSATION @.dialog;
RETURN
END
-- Error?
IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
SET @.ErrorMessage = CAST(@.message_body as nvarchar(max));
RAISERROR( @.ErrorMessage, 1, 1);
RETURN;
END
-- *** Proceed ...
-- *** This will run successfully despite @.@.ROWCOUNT = 0
-- Extract the event information using XQuery.
SET @.SetId = @.message_body.value('(/UpdateState/SetId)[1]', 'int');
SET @.State = @.message_body.value('(/UpdateState/State)[1]', 'smallint');
UPDATE CustomerSetMap SET State = @.State WHERE SetId = @.SetId AND (State = 1 or State = 9);
-- END CONVERSATION @.dialog;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;
EXEC LogError
END CATCH
END
|||Hello Roger,
If the ShredSurveySet throws an exception which leaves the transaction commitable, the XACT_STATE() will be 1. Your CATCH block will not commit this transaction and as such the procedure will exit with an unbalanced BEGIN TRY/COMMIT pair, resulting in a nastygram in the ERRORLOG:
2006-11-06 14:14:32.69 spid52s The activated proc [dbo].[ShredSurveySet] running on queue testNG.dbo.SurveySetQueue output the following: 'Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.'
Do you have any such messages in your ERROLOG? BTW, an example of a situation that throw an exception that leaves the pending transaction context commitable is trying to SEND a NULL message body...
As about the sender's procedure never getting a @.@.ROWCOUNT, it seems to me you're never activated because of a incomming message. There are cases when activation will start the procedure even though there are no messages to receive and it seems your hitting such case.
My recommendation would be to test the procedures first manually, w/o activation. Send a message, then run the target's procedure by calling manualy EXEC, verify the response was sent (SELLECT ... FROM ParseRequestsQueue) and then run the sender's procedure manually. Only after you're satisfied with this results turn on activation.
HTH,
~ Remus
I hit exactly this issue because I expected the activation procedure only to be activated if there were messages to consume. What I found that was that activation occurs when the message is received, my sp processed the message on the queue and completed. Activation the fire up another SP almost as a catch all, I wasn't verifying that a message was actually received from the queue so my logic fired twice.
So the morale of the story is, always check the @.@.rowcount after receiving a message
