HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Cannot get a message to go to a SQL Server Service Broker Queue

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cannotsqlmessagebrokergetserviceserverqueue

Problem

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'Message for tgt service';

-- 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

Solution

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

use ;
go

select transmission_status, * 
from sys.transmission_queue;
go


The status will tell what the issue is.

Code Snippets

use <testdb>;
go

select transmission_status, * 
from sys.transmission_queue;
go

Context

StackExchange Database Administrators Q#14567, answer score: 7

Revisions (0)

No revisions yet.