SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Service Broker (2005)
 Where am I going wrong?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PhilSchmidt
Starting Member

5 Posts

Posted - 04/28/2010 :  17:52:38  Show Profile  Reply with Quote
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 - 08/04/2010 :  11:58:01  Show Profile  Reply with Quote
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/
Go to Top of Page

britneys
Starting Member

1 Posts

Posted - 09/10/2010 :  04:01:20  Show Profile  Visit britneys's Homepage  Reply with Quote
spam removed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000