Don't get Service Broker to Work

Hi,

I'm not able to get Service Broker to work. I've created the following sample and would excpect to get some data from "PreisanfrageQueue" or "PreisanfrageRequestorQueue". But both they are emtpy.

What do I do wrong?

Regards,

Manfred

create message type Preisanfrage
validation = well_formed_xml;

create message type PreisanfrageAntwort
validation = well_formed_xml;

create contract PreisanfrageContract
(
Preisanfrage sent by initiator,
PreisanfrageAntwort sent by target
);

create queue PreisanfrageRequestorQueue with
status=on;

create queue PreisanfrageQueue;

create service PreisanfrageRequestorService
on queue PreisanfrageRequestorQueue ( PreisanfrageContract );

create service PreisanfrageService
on queue PreisanfrageQueue (PreisanfrageContract );

create table debug_table;
create table debug_table (id int primary key identity(1,1), msg varchar(100));

create procedure PreisanfrageAction
as
declare @.conversation uniqueidentifier
declare @.msg nvarchar(max)
declare @.msgType nvarchar(256)
declare @.answer xml;

insert into debug_table(msg) values('1');

;receive top(1)
@.conversation = conversation_handle,
@.msg = message_body,
@.msgType = message_type_name
from PreisanfrageQueue;

insert into debug_table(msg) values('2');

-- Preisanfrage bearbeiten

set @.answer = '<preis>1</preis>';
;send on conversation @.conversation
message type PreisanfrageAntwort (@.answer);

end conversation @.conversation;

insert into debug_table(msg) values('3');

alter queue PreisanfrageQueue
with
status=on,
activation (
status=on,
PROCEDURE_NAME = PreisanfrageAction,
max_queue_readers = 100,
EXECUTE AS OWNER
);

-- Dialog starten

declare @.conversation uniqueidentifier;

begin dialog conversation @.conversation
from service [PreisanfrageRequestorService]
to service 'PreisanfrageService'
on contract [PreisanfrageContract];

declare @.request xml;

set @.request = '<?xml version="1.0" encoding="UTF-8"?><Preisanfrage xmlns="4711101'">http://www.xyz.at/samples/Preisanfrage"><KundenId>4711</KundenId><ProduktId>10</ProduktId><Anzahl>1</Anzahl></Preisanfrage>';

;send on conversation @.conversation
message type Preisanfrage ( @.request );

receive * from PreisanfrageQueue;

receive * from PreisanfrageRequestorQueue;

select * from debug_table

1. Please double check you have created the master key in this database.

create master key

encryption by password='asdfasdf'

2. query the system table to check the message’s transmission status.

select * from sys.transmission_queue

3. Don’t use

receive * from PreisanfrageQueue;

receive * from PreisanfrageRequestorQueue;

To check the queue's messenger, because this will remove the message from

the queue and but the conversation open

|||

Hi,

thanks - it was the missing master key.

Did I get it right, that thouse activation procedures are polling the queue ?
If yes - is there a way to use some kind of notification so that the activation procedure is just called every time when a message is enqueued ?

Best whishes,
Manfred

|||You did it correctly, but your messages are getting taken off as soon as you put them on there, through your receive * statements. You should try to receive one at a time and do something with those records.