SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Introduction to the Service Broker


An Introduction to the Service Broker

Author
Message
Patrick-304111
Patrick-304111
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 81

I couldn't get any result too.

Anyway, thanks for sharing your exp.

Patrick


sql05guy
sql05guy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 35

I was not getting any results either in a new database until I ran this script to enable the serice broker in my database:

ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER

from 2005 books online:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ac7e4c7c-e52f-4883-8f3c-9336cc77a9c8.htm

hope that helps....


espasojevic
espasojevic
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 124
It is enabled in my database, and still nothing.



sql05guy
sql05guy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 35

Here are the steps that worked (finally) for me:

  1. Login as 'sa' on your local sql 2005 server.
  2. Create a new database
  3. In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)
  4. Copy and paste script from 'Intro...to Service Broker' article into a new query window.
  5. Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.
  6. Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])
  7. Run entire script on your newly created database and it should return the 'Hello world' message.

If not, do a select * from sys.transmission_queue to see what errors were generated.

Hope that helps....


stevemc
stevemc
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 684

I finally got this to work. How do I get rid of messages in sys.transmission_queue?


Peter Pirker
Peter Pirker
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 221
Great article, but something went wrong when I ran the sample code - it returns an empty message.

??

BR

Peter Pirker
kingshukbagchi
kingshukbagchi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 16
Hi Srinivas...this was an excellent pice of code... can I pls. have your mail id?? I've some further queries to be clarified..my mail ids are : kbagchi@careindia.org & kingshukbagchi@rediffmail.com .

Thnaks is advance,

Kingshuk.
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12616 Visits: 3766

Here are the steps that worked (finally) for me:

Login as 'sa' on your local sql 2005 server.
Create a new database
In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)
Copy and paste script from 'Intro...to Service Broker' article into a new query window.
Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.
Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])
Run entire script on your newly created database and it should return the 'Hello world' message.
If not, do a select * from sys.transmission_queue to see what errors were generated.

Hope that helps....


I ran the following:


ALTER DATABASE AdventureWorks SET ENABLE_BROKER


Then, after running the following, I still get no results:



-- We will use adventure works as the sample database
USE AdventureWorks
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
WITH ENCRYPTION=OFF, LIFETIME= 600;
-- 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


__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12616 Visits: 3766
Here are the steps that worked (finally) for me:

Login as 'sa' on your local sql 2005 server.
Create a new database
In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)
Copy and paste script from 'Intro...to Service Broker' article into a new query window.
Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.
Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])
Run entire script on your newly created database and it should return the 'Hello world' message.
If not, do a select * from sys.transmission_queue to see what errors were generated.

Hope that helps....




Thanks, it does work on a new db, but why does it not work on AdventureWorks, even after enabling the Service Broker??

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
kingshukbagchi
kingshukbagchi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 16
This has actually worked for me... have you done "alter database [database name] set enable_broker"?

Kingshuk
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search