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 / March 2006

Tip: Looking for answers? Try searching our database.

No luck trying to use simple sample of SB

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tom Wilson - 08 Mar 2006 18:30 GMT
I'm just starting to explore SB.  I've tried a couple of sample scripts that
I've found in various tutorials, but having no luck.  Following is the
simplest one I've tried.  All steps run just fine, but message appears in
the RECEIVE section.

Additional notes:  tried on two different SQL 2005 servers.  Both running in
a named instance - is there any additional configuration required for this?

Thanks in advance.
Tom

Sample code follows.

create database TestSB
go
USE TestSB
GO
-- 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
-- Receive a message from the queue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue
-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO
Remus Rusanu [MSFT] - 08 Mar 2006 18:44 GMT
Try following the steps in this mini troubleshooting guide:
http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx to identify
the problem.

I expect you'll find the messages in the sys.transmission_queue with a
transmission_status complaining about lack of database master key, since you
are attempting to use secure dialogs.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

> I'm just starting to explore SB.  I've tried a couple of sample scripts
> that I've found in various tutorials, but having no luck.  Following is
[quoted text clipped - 67 lines]
> DROP MESSAGE TYPE HelloMessage
> GO
Tom Wilson - 08 Mar 2006 19:13 GMT
That did it - no master key.  Just to close the loop for anyone else looking
at the sample script below, the begin dialog section needs to be modified to
the following, if no encryption key has been defined for the database.

 BEGIN DIALOG @conversationHandle
       FROM SERVICE Sender
       TO SERVICE 'Receiver'
       ON CONTRACT HelloContract
 WITH ENCRYPTION = OFF        -- additional line

> Try following the steps in this mini troubleshooting guide:
> http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx to
[quoted text clipped - 76 lines]
>> DROP MESSAGE TYPE HelloMessage
>> GO
 
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.