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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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  
 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