debugsqlMinor
Cannot get a message to go to a SQL Server Service Broker Queue
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.
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;
GOSolution
Look at the
The status will tell what the issue is.
transmission_status column in sys.transmission_queue in your database:use ;
go
select transmission_status, *
from sys.transmission_queue;
goThe status will tell what the issue is.
Code Snippets
use <testdb>;
go
select transmission_status, *
from sys.transmission_queue;
goContext
StackExchange Database Administrators Q#14567, answer score: 7
Revisions (0)
No revisions yet.