October 30, 2008 at 7:43 am
h.schlais (10/30/2008)
Should the END CONVERSATION be wrapped in an IF to avoid ending a conversation with a NULL handle ?
Well, if should be protected by an IF. I usually just do an "IF @hndl IS NULL RETURN" right after the RECEIVE.
For various reasons, it is normal for a Queue's activation procedure to be activated more often than there are unique conversations pending for it.
Why is the above normal, what's going on there ?
I am not 100% sure, but I believe that the Service Broker automatically does one activation for every message in a queue, however, the activation procedures typically RECEIVE more than one message each. Specifically, "END CONVERSATION" from the initiator puts and "END" type message on the Queue (which cause another activation), but the Responder's "END CONVERSATION" dequeue's the same "END" message, leaving an extra activation with no messages left.
This is fairly typical for message-based architectures and in order to be robust, the responders need to assume nothing, check everything and tolerate benign "exceptions" like this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 30, 2008 at 8:50 am
Thank you for the explanation.
If I'm not imposing on your good will below is the code I used to create the messages, queues, et.al. and below that is the procedure that allegedly starts the conversation. These all, on the surface, seem to work but the target procedure is not activated at all, not even with a NULL conversation handle. Is it because of the way I create the message? Warm regards,
-- enable service broker
IF ((SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID()) = 0)
ALTER DATABASE hcsdb_dev SET ENABLE_BROKER;
-- create message types and contracts
IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = '//My-Domain.COM/TESTRequest')
BEGIN
-- request message
CREATE MESSAGE TYPE
[//My-Domain.COM/TESTRequest]
VALIDATION = WELL_FORMED_XML
END
IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = '//My-Domain.COM/TESTResponse')
BEGIN
-- response message
CREATE MESSAGE TYPE
[//My-Domain.COM/TESTResponse]
VALIDATION = WELL_FORMED_XML
END
IF NOT EXISTS (SELECT * FROM sys.service_contracts WHERE name = '//My-Domain.COM/TESTContract')
BEGIN
-- contract
CREATE CONTRACT
[//My-Domain.COM/TESTContract]
(
[//My-Domain.COM/TESTRequest]
SENT BY INITIATOR,
[//My-Domain.COM/TESTResponse]
SENT BY TARGET
)
END
-- queues and services
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TESTRequestQueue')
BEGIN
-- request queue
CREATE QUEUE TESTRequestQueue
WITH STATUS = ON,
ACTIVATION (STATUS = ON,
PROCEDURE_NAME = dbo.z_TEST_Target_DEV,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
END
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TESTResponseQueue')
BEGIN
-- response queue
CREATE QUEUE TESTResponseQueue
WITH STATUS = ON
END
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = '//My-Domain.COM/TESTRequestService')
BEGIN
-- request service
CREATE SERVICE
[//My-Domain.COM/TESTRequestService]
ON QUEUE TESTResponseQueue
([//My-Domain.COM/TESTContract])
END
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = '//My-Domain.COM/TESTResponseService')
BEGIN
-- response service
CREATE SERVICE
[//My-Domain.COM/TESTResponseService]
ON QUEUE TESTRequestQueue
([//My-Domain.COM/TESTContract])
END
USE [hcsdb_dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[z_TEST_Initiator_DEV]
(
@ProjectID INT = 0,
@Rep INT = 0,
@Year INT = 0,
@Frequency CHAR(1) = 'M'
)
AS
BEGIN
SET NOCOUNT ON
SET @ProjectID = NULLIF(@ProjectID,0)
SET @Rep = NULLIF(@Rep,0)
SET @Frequency = NULLIF(@Frequency,'')
IF (NULLIF(@Year,0) IS NULL)
SET @Year = (DATEPART(YEAR, GETDATE())-1)
--create @Filepath so this procedure can pass it to the next procedure
DECLARE @FilePath VARCHAR(500)
SET @FilePath = '\\SQL_SERVER\hcs\ServiceBrokerTest.txt'
DECLARE @dialog_handle UNIQUEIDENTIFIER
DECLARE @message VARCHAR(MAX)
SET @message = '@ProjectID='+CONVERT(VARCHAR(10),@ProjectID)
+', @Rep='+CONVERT(VARCHAR(10),@Rep)
+', @YEAR='+CONVERT(VARCHAR(10),@Year)
+', @Frequency='+@Frequency
+', @Filepath='+@Filepath
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [//My-Domain.COM/TESTRequestService]
TO SERVICE 'TESTResponse'
ON CONTRACT [//My-Domain.COM/TESTContract]
WITH ENCRYPTION = OFF
;SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [//My-Domain.COM/TESTRequest] (@message)
SET NOCOUNT OFF
END
October 30, 2008 at 12:26 pm
I am between meetings here, but let me quickly call out three suggestions:
1) See my Blog (in my signature) or just downlaod this zip file: http://www.movingsql.com/dnn/LinkClick.aspx?fileticket=c7EJbXre%2fLg%3d&tabid=125&mid=461 which the slides and code from my recent Code Camp presentation "The Top 10 Reason You Aren't Already Using Service Broker". Basically it is a guide to the road blocks we typically run into on our first Service Broker effort, and how to get around them. In fact, I suspect that you are running into 2 of them right now.
2) The first is "Dude, Where's My Error?" wherein no errors (other than syntax errors) are ever reported to the Sender, but niether do the messages ever arrive. Because of the asynchronous nature of Service Broker, it does not return errors in the Sender's process, but rather places them in one of three different places. In this case, they probably can be found with this command:Select * From sys.transmission_queue, look in the transmission_status column (the last one).
3) "Master Key is NOT Optional": for some reason, BOL, and virtually every SB example in existence claim that the DB Master Key is optional for Service Broker. But in my experience, it is not at all optional: I have never been able to get service broker to work without it. See my code examples or BOL for sample syntax, or I could post an example later on.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 30, 2008 at 3:02 pm
This will take a bit to digest. I will be back though. Warm regards,
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply