I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.
I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.
This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.
I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."
I'm hoping someone can point me in the right direciton, thanks.
 Larry Grady wrote: |
| |
| Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry |
Enable the destination queue using ALTER QUEUE [<queuename>] WITH STATUS = ON; During development queues often become disabled as a result of poison message prevention mechanism noticing rollbacks, see http://technet.microsoft.com/en-us/library/ms171592.aspx
|||
Ok, so it looks like the problem is before this. The real question is why is my queue being disabled. Because when I alter the queues to have WITH STATUS=ON or if I delete teh queues and services and recreate, when I run it the first time, my queue gets disabled. I'm not sure why, can't seem to find a good error message.
Here is profiler on that first run
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Message Classify Microsoft SQL Server Management Studio - Query
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_INBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Activation Microsoft SQL Server Management Studio - Query
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Queue Disabled
Broker:Activation
I get no results when I run SELECT * FROM sys.transmission_queue
Any idea why my que is disabling or where i can get a better message letting me know what's going on. This is my first attempt that this so it could be something simple I'm missing.
I used that original example and only changed the send and receive stored procedures and dbase names. No other administration was done.
|||Your activated procedure is rolling back and causing the queue to be disabled. Check ERRORLOG for error messages from the activated procedure. Turn off activation and run the procedure manually to catch it's output and debug it.|||
Hey!
In the future if you have any problems you're welcome to post the error in the comments section of that article because i don't really check here so often. I'm practicaly online all the time on SQLTeam during the work day so i'd also be able to answer any question you might have.
but as i see it Remus has it handled pretty well around here so no worries 
Mladen
|||
Thanks, these posts helped a lot.
I had changed the XML structure and the audit table/insert from the article to meet my biz needs and as a result I wasn't querying the msgBody correctly. However, I had no idea why my queue was disabled when I ran my example. It seemed more like an administration or permission error to me, so that's what I was concentrating on. It wasn't until I started stepping through, running pieces of the sprocs on their own that I found it.
But there wasn't any kind of informative error letting me know what the problem was on the target side. I think perhaps I need to institute the same kind of error handling that is in the initiating sproc into the targe sproc. Then if there is a logic error I can have some kind of information about it.
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
Anyway, I have my basic example working and it seems to work great. Great example articles, thanks.
|||
| Larry Grady wrote: |
| |
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down. |
In production you should monitor queues using event notifications for QUEUE_DISABLED event. This event is fired when a poison message disables a queue and you can react to the event (eg. notify the site administrator)|||
also in my example the error that causes the activation proc to rollback gets saved in the AuditErrors table.
you might want to check there for troubleshooting.
|||
My project is moving along nicely. I have my queues working properly and i'm writing data to my audit tables.
I have one more issue, although it's not directly SSB related, it is part of this project.
My main stored procedure is a product search. For each product search we have to save the Criteria that was used and then we have to save the products that were returned. The stored procedure itself is a really long and complicated proc that builds a dynamic sql string. Then at the end it executes it.
The way i ahve it working now
..... Process to build @.SQLString.....
EXEC(@.SQLString)
This returns my search results. Now i've added after it my initiator query. This query creates the AuditMsg in XML and loads it into a message which is que'd and sent to my target database where this data is inserted into an audit table.
EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source
This is where i'm a little stuck. I need to then take the output, the actual records that are returned, and store 3 of the returned fields into an audit table. The results can be anywhere from a few records to a couple thousand.
I was thinking of INSERTING into a temp table and then sending that and doing a SELECT FOR XML to send to the audit table and then doing a SELECT @.tempResults to return from the main stored procedure. I don't know if that's really teh best way to do this. Anyone have any suggestions. I'm kind of struggling with the right way to do this. A few lines of pseudo code may go a long way. Thanks.
|||
i'm not quite sure i understand what you're trying to do...
could you post some sample code or pseudo code?
|||
Here is my initial search query (actually much of the dyamic part is stripped out because it doens't apply to the problem).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spResults_Select]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.priceFrom real,
@.priceTo real,
[MORE CRITERIA]
@.bHiResImg int
)
AS
BEGIN
[LOTS OF CODE HERE BUILDING SQL STRING. Actually a TEMP table is build and then we select off of that temp table, it's very complicated. We plan on optimizing later but for now it is what it is]
[the string is @.SelectSQL]
EXEC(@.SelectSQL)
[This is where the original stored procedure ended. This EXEC line returns the search results back to the Search form]
[New Code: This stored procedure is an initiator query that takes the parameters, formulates it into XML, creates a messages and send it to SSB. This is for the search criteriea, this is working]
EXEC usp_SendSearchAudit
@.userID=@.userID,
@.brandList=@.brandList,
@.subCatList=@.subCatList,
@.keywordSearch=@.keywordSearch,
@.priceFrom=@.priceFrom,
@.priceTo=@.priceTo,
@.bHiResImg=@.bHiResImg,
@.source=@.source
[This is where I am stuck. EXEC(@.SelectSQL) returns a bunch of records to the searchresults object. However, I need to take those same records and extract the Product_ID from each and save it to the audit tables in teh other database. I am going to create XML object out of it, create a message, send it to SSB).
Something like
EXEC ups_SendSearchResultsAudit
[parameters]
Or, maybe It would all be a part of the usp_SendSearchAudit above, that I would pass a TABLE parameter into that stored procedure?
END
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SendSearchAudit]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.keywordSearch varchar(2000),
@.priceFrom real,
@.priceTo real,
@.bHiResImg int,
@.source varchar(10)
)
AS
BEGIN
DECLARE @.AuditMsg XML
SELECT @.AuditMsg = '<AuditMsg MsgType="Search">
<Search>
<userID>' + @.userID + '</userID>
<brands>' + (SELECT 1 as Tag, NULL as Parent, gID [brandID!1] FROM dbo.f_ConvertGuidList_to_Table(@.brandList) Brands FOR XML EXPLICIT) + '</brands>
<subCats>' + (SELECT 1 as Tag, NULL as Parent, string [subCatID!1] FROM dbo.f_ConvertStringList_to_Table(@.subCatList) SubCats FOR XML EXPLICIT) + '</subCats>
<keywords>' + @.keywordSearch + '</keywords>
<priceFrom>' + CONVERT(varchar(10),@.priceFrom) + '</priceFrom>
<priceTo>' + CONVERT(varchar(10),@.priceTo) + '</priceTo>
<hiRes>' + CONVERT(varchar(10),@.bHiResImg) + '</hiRes>
<source>' + @.source + '</source>
</Search>
</AuditMsg>'
EXEC dbo.usp_SendAuditData @.AuditMsg
--SELECT @.AuditMsg
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
ALTER PROCEDURE [dbo].[usp_SendAuditData]
(
@.AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @.dlgId UNIQUEIDENTIFIER
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @.dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'X1X1X1X1-X1X1-X1X1-X1X1-X1X1X1X1X1X1'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
;SEND ON CONVERSATION @.dlgId
MESSAGE TYPE [//Audit/Message] (@.AuditedData)
END TRY
END
I have stripped out a ton of code that doesn't really pertain, but this is basically what i'm doing.
|||
you know... that is the coolest GUID i've seen in a while! 
well if a temp table is created then you could select from that temp table.
for temp tabel scopes read this post of mine:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx
you can also use a global temp table with a guid for name to avoid problems.
