You have this check for your re-used conversation handle being either NULL, non-existent or bad state:
IF @dlgId IS NOT NULL
AND NOT EXISTS( SELECT * FROM sys.conversation_endpoints
WHERE conversation_handle = @dlgId AND state IN ('SO', 'CO')
)
But it appears that you only handle the NULL case:
IF @dlgId IS NULL
BEGIN -- Begin the dialog, with the new Id
BEGIN DIALOG CONVERSATION @dlgId
FROM SERVICE [tcp://10.0.0.89:4022/db_ActivityManagement/Audit/DataSender]
TO SERVICE '//Audit/AcMan_DataWriter',
-- this is a MasterAuditDatabase Service Broker Id
-- (change it to yours and remove
'51E3A8D4-BE8B-4162-8B90-245E321F674A'
ON CONTRACT [//Audit/AcMan_Contract]
WITH ENCRYPTION = OFF;
-- add our db's dialog to AuditDialogs table if it doesn't exist yet
INSERT INTO dbo.tbl_AuditDialog(tlg_dbID, tlg_dialogID)
SELECT DB_ID(), @dlgId
END
-- Send our data to be audited
;SEND ON CONVERSATION @dlgId
MESSAGE TYPE [//Audit/AcMan_rMessage] (@AuditedData)
Note that if it is non-existent or in a bad state, nothing is done, but the SEND still runs and tries to use it.
[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]