Trying to understand how the service broker works, through a hello
world type sample (TSQL's below).
A select on the target queue does not give back any results. Any
clues?
Regards,
Rohit
-- We will use adventure works as the sample database
USE AdventureWorks
GO
--ALTER DATABASE [AdventureWorks] SET ENABLE_BROKER
-- First, we need to create a message type. Note that our message type
is
-- very simple and allowed any type of content
CREATE MESSAGE TYPE HelloMessage
VALIDATION = NONE
GO
-- Once the message type has been created, we need to create a
contract
-- that specifies who can send what types of messages
CREATE CONTRACT HelloContract
(HelloMessage SENT BY INITIATOR)
GO
-- The communication is between two endpoints. Thus, we need two
queues to
-- hold messages
CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO
-- Create the required services and bind them to be above created
queues
CREATE SERVICE Sender
ON QUEUE SenderQueue
CREATE SERVICE Receiver
ON QUEUE ReceiverQueue (HelloContract)
GO
-- At this point, we can begin the conversation between the two
services by
-- sending messages
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
-- Send a message on the conversation
SET @message = N'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE HelloMessage (@message)
COMMIT TRANSACTION
END
GO
select * from ReceiverQueue --This statement does not give back any
results
Bob Beauchemin - 17 Jul 2007 17:00 GMT
Hi Rohit,
Service Broker uses encryption by default, even between services in the same
database, and needs to be able to generate a session key. So you need to
either:
1. Change BEGIN DIALOG to include "WITH ENCRYPTION=OFF"
2. Create a database master key in the database you are using.
You should be able to verify that the message is stuck in the
transmission_queue by doing a "select * from sys.transmission_queue". The
last field is the reason for non-delivery. And, if this is the problem and
you do #2 above, the original message should be delivered eventually.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
> Trying to understand how the service broker works, through a hello
> world type sample (TSQL's below).
[quoted text clipped - 57 lines]
> select * from ReceiverQueue --This statement does not give back any
> results