Service Broker

[DaPhotoGuy]

I just can't seem to get any messages to show up in the destination queue. I've set this up before but for some reason I can't get it to work this time. I'm using SQL version 9.00.4262.00 Microsoft SQL Server Standard Edition (64-bit). The databases are on the same server. I'm going blind looking at this. Can someone please help me. Here is my code. . . .

USE MASTER
GO

--Source Database
ALTER DATABASE IdentityDev SET ENABLE_BROKER
GO

--Destination Database
ALTER DATABASE IdentityServiceBrokerDev SET ENABLE_BROKER
GO

ALTER DATABASE IdentityDev SET TRUSTWORTHY ON;
GO

ALTER DATABASE IdentityServiceBrokerDev SET TRUSTWORTHY ON;
GO

USE IdentityServiceBrokerDev;
GO

CREATE MESSAGE TYPE [//BothDB/Identity/RequestMessage]
VALIDATION = NONE;
CREATE MESSAGE TYPE [//BothDB/Identity/ReplyMessage]
VALIDATION = NONE;
GO


CREATE CONTRACT [//BothDB/Identity/SimpleContract]
([//BothDB/Identity/RequestMessage]
SENT BY INITIATOR,
[//BothDB/Identity/ReplyMessage]
SENT BY TARGET
);
GO


CREATE QUEUE IdentityTargetQueue;

CREATE SERVICE [//TgtDB/Identity/TargetService]
ON QUEUE IdentityTargetQueue
([//BothDB/Identity/SimpleContract]);
GO

USE IdentityDev;
GO

CREATE MESSAGE TYPE [//BothDB/Identity/RequestMessage]
VALIDATION = NONE;
CREATE MESSAGE TYPE [//BothDB/Identity/ReplyMessage]
VALIDATION = NONE;
GO


CREATE CONTRACT [//BothDB/Identity/SimpleContract]
([//BothDB/Identity/RequestMessage]
SENT BY INITIATOR,
[//BothDB/Identity/ReplyMessage]
SENT BY TARGET
);
GO


CREATE QUEUE IdentityInitiatorQueue;

CREATE SERVICE [//InitDB/Identity/InitiatorService]
ON QUEUE IdentityInitiatorQueue;
GO

----------------------------------------------------


USE IdentityDev;
GO


DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG CONVERSATION @InitDlgHandle
FROM SERVICE [//InitDB/Identity/InitiatorService]
TO SERVICE '//TgtDB/Identity/TargetService'
ON CONTRACT [//BothDB/Identity/SimpleContract]
WITH ENCRYPTION=OFF;

SELECT @InitDlgHandle

SELECT @RequestMsg =
'<RequestMsg>Message for Target service.</RequestMsg>';


SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//BothDB/Identity/RequestMessage]
(@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

---------------------------

--THIS IS THE PART THAT DOESN'T WORK. DOESN'T SEEM TO BE ANYTHING IN THE QUEUE.

USE IdentityServiceBrokerDev;
GO

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM IdentityTargetQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName =
'//BothDB/Identity/RequestMessage'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE
[//BothDB/Identity/ReplyMessage] (@ReplyMsg);

END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

--------------------------------------------

USE IdentityDev;
GO

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body
FROM IdentityInitiatorQueue
), TIMEOUT 1000;

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

----------------------------------------------------

 

[mrdenny]

What shows in the sys.transmission_queue in both databases?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[DaPhotoGuy]

Thanks for the tip. That is where I discovered the problem a few days ago. It ended up being a Master Key issue.