Service Broker Sample Code

Last Post 15 Apr 2007 05:12 PM by SQLUSA. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sugarBABY
New Member
New Member

--
21 Feb 2007 04:57 PM
hi

I have this sample code from a book. I tried the code but is not showing anything as what the book has said. i am using sql 2005 devloper edition. Only one instance. Any help? Thanks

IF NOT EXISTS
(SELECT * FROM sys.databases
WHERE name = 'AdventureWorks'
AND is_broker_enabled = 1)
BEGIN
ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
END



-- Create the XML SBSampleMessage message type
CREATE MESSAGE TYPE SBSampleMessage
VALIDATION = WELL_FORMED_XML ;
GO
-- Create the SBSampleContract contract
CREATE CONTRACT SBSampleContract
( SBSampleMessage SENT BY INITIATOR);
GO
-- Create the queue
CREATE QUEUE [dbo].[ReceiverQueue];
GO
-- Create the queue for the Sender service
CREATE QUEUE [dbo].[SenderQueue];
GO
-- Create the service
CREATE SERVICE SenderService
ON QUEUE [dbo].[SenderQueue];
GO
-- Create the target service
CREATE SERVICE ReceiverService
ON QUEUE [dbo].[ReceiverQueue]
(SBSampleContract);
GO

--No message in the queue at this moment
SELECT * FROM dbo.ReceiverQueue

USE AdventureWorks ;
GO
-- Begin a transaction
BEGIN TRANSACTION ;
GO
-- Declare a variable for the message
DECLARE @SBmessage XML ;
SET @SBmessage = N'<message>Service Broker is Cool</message>' ;
-- Declare a variable for the conversation ID
DECLARE @conversationID UNIQUEIDENTIFIER ;
-- Begin a dialog between the services
BEGIN DIALOG CONVERSATION @conversationID
FROM SERVICE SenderService
TO SERVICE 'ReceiverService'
ON CONTRACT SBSampleContract ;
-- Put the message on the queue
SEND ON CONVERSATION @conversationID
MESSAGE TYPE SBSampleMessage
(@SBmessage) ;
-- End the conversation
END CONVERSATION @conversationID ;
GO
-- Commit the transaction to send the message
COMMIT TRANSACTION ;
GO


--Show 2 rows
USE AdventureWorks ;
GO
SELECT * FROM [dbo].[ReceiverQueue]

USE AdventureWorks ;
GO
SELECT CAST(message_body as nvarchar(MAX)) from ReceiverQueue
sugarBABY
New Member
New Member

--
22 Feb 2007 12:38 AM
hi

Publisher is Osborne. Book title: A developer guide to sql 2005 and is page 116.

I follow the step on that book.

No result from queue.
sugarBABY
New Member
New Member

--
22 Feb 2007 07:21 AM
no error but just no message and i do not have any ideal what went wrong

sorry the title was: Microsoft Sql Server 2005: A Developer Guide by McGraw Hill Osborne
DianaMay
New Member
New Member

--
06 Apr 2007 03:02 PM
Is this the one by author. Michael Otey? If so, I could send this off to Mike.
SQLUSA
New Member
New Member

--
15 Apr 2007 05:12 PM
First it was stuck with ALTER TABLE because I had some other sessions open to AdventureWorks.

This command needs an exclusive lock.

After closing the session, the script ran, but returned no results.

Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com/order2005
You are not authorized to post a reply.

Acceptable Use Policy