Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to setup a simple SQL Server Service Broker messaging setup. I am following the MS tutorials, but I cannot get the following code to work. Everything runs fine, but the message does not appear on the queue. Can anyone identify the problem.

Thanks for any help.

-- This query returns "1"
SELECT
    Is_Broker_Enabled
FROM Sys.Databases
WHERE Name = 'ActivationTest2';
GO

-- Create the message types.
CREATE MESSAGE TYPE TestRequestMessage VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE TestReplyMessage VALIDATION = WELL_FORMED_XML;

-- Create the contract.
CREATE CONTRACT TestContract (
    TestRequestMessage SENT BY INITIATOR, 
    TestReplyMessage SENT BY TARGET) ;

-- Create the target queue and service.
CREATE QUEUE ActTestTgtQueue;
CREATE SERVICE ActTestTgtService ON QUEUE ActTestTgtQueue;

-- Create the initiator queue and service.
CREATE QUEUE ActTestInitQueue;
CREATE SERVICE ActTestInitService ON QUEUE ActTestInitQueue;
GO

-- Start a conversation, send the request message.
DECLARE
    @initdlghandle uniqueidentifier,
    @requestmsg nvarchar (100) ;

BEGIN TRANSACTION;

BEGIN DIALOG @initdlghandle 
    FROM SERVICE ActTestInitService 
    TO SERVICE 'ActTestTgtService' 
    ON CONTRACT TestContract 
    WITH ENCRYPTION = OFF;

SET @requestmsg = N'<RequestMsg>Message for tgt service</RequestMsg>';

-- This returns a GUID and the message text above.
SELECT
    @requestmsg AS Sentrequestmsg
  , @initdlghandle AS Handle;

SEND ON CONVERSATION @initdlghandle 
    MESSAGE TYPE TestRequestMessage (@requestmsg) ;

COMMIT TRANSACTION;
GO
-- Check the queue for messages.
-- THIS RETURNS ZERO ROWS.
SELECT
    *
FROM ActTestTgtQueue;
GO
share|improve this question
    
add comment

migrated from stackoverflow.com Mar 7 '12 at 16:48

This question came from our site for professional and enthusiast programmers.

2 Answers

Look at the transmission_status column in sys.transmission_queue in your database:

use <testdb>;
go

select transmission_status, * 
from sys.transmission_queue;
go

The status will tell what the issue is.

share|improve this answer
    
The transmission_status is: "Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation." –  Sako73 Mar 7 '12 at 2:12
    
Sry didn't have time to reply on time. The error message could be also discovered in your initiator queue: SELECT cast(message_body as XML), * FROM ActTestInitQueue. At least you now know not only what the problem was, but also how to Troubleshoot Dialogs –  Remus Rusanu Mar 7 '12 at 5:19
add comment

Well, after a very frustrating evening, I found the answer.

Thank you Remus for your help.

I took the advice in THIS LINK, and in the "Broker:Message Undeliverable" EventClass in SQL Server Profiler, I finally found a more descriptive error message: "This message could not be delivered because the targeted service does not support the service contract. Targeted service: 'ActTestTgtService', service contract: 'TestContract'."

Looking at the documentation HERE, if you do not specify the contract that a service can communicate with, it can only initiate messages, not be the target. Once I changed the following line, it worked fine:

create service ActTestTgtService on queue ActTestTgtQueue (TestContract);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.