Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Service Broker / July 2007

Tip: Looking for answers? Try searching our database.

Unable to read from the queue

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rohit - 17 Jul 2007 02:19 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.