Problem with Service Broker - getting it to work

Question

Post reply

Problem with Service Broker - getting it to work

ben schwartz`

SSC Enthusiast

Points: 128
More actions
June 3, 2008 at 8:15 am

#186330

Hi... trying to get Service Broker to work...there are a couple of examples on the net that i tried i.e.. http://www.sqlservercentral.com/articles/Development/anintroductiontotheservicebroker/1957/
but it did not work... When i looked further i saw the command
ELECT is_broker_enabled FROM sys.databases WHERE name = 'FundControl' the flag was set to '0'
So tried to do
use master
go
alter database fundcontrol set enable_broker
the command 'hangs' the executing status never goes away... what am i missing ?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ben schwartz` SSC Enthusiast Points: 128 More actions · Answer 1

Ok i did find this;

ALTER DATABASE db_name

SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

GO

which worked .... ENABLE_BROKER is now set to 1.... However

the example is still not working all the commands work as listed below. But

the RECEIVE CONVERT(NVARCHAR(max), message_body) AS message

FROM ReceiverQueue

Shows nothing in the display window... Need help again

-- 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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

If you execute this command:

select * from sys.transmission_queue

You will probably see that the transmission_status column has the following text: "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

If so, then execute this command:

create master key encryption by password = 'Password1'

Change 'Password1' to whatever password you want.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ben schwartz` SSC Enthusiast Points: 128 More actions · Answer 3

MR. SSCommitted Thank you very, very much you are 100% correct.. i saw that but i did not see that...thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

ben schwartz` (6/4/2008)
MR. SSCommitted Thank you very, very much you are 100% correct.. i saw that but i did not see that...thanks

Heh. thanks, I think.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]