Where am I going wrong? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Service Broker (2005)

Where am I going wrong?

Author

Topic

PhilSchmidt
Starting Member

5 Posts

Posted - 2010-04-28 : 17:52:38

I just can't seem to get any messages to show up in the destination queue. 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

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

Liam
Starting Member

6 Posts

Posted - 2010-08-04 : 11:58:01

your probably onto other things by now, i dont have time to test your code but i can suggest comparing it to these examples which i found useful

http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/

britneys
Starting Member

1 Post

Posted - 2010-09-10 : 04:01:20

spam removed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources