Frustrated with Service Broker Example

I want to use a Service Broker Queue as a processing queue. I found several examples on the WEB and have tried to make them work but they do not. Below is the example I used and I ASSUME I should get a Hello World in the Message field at the end but its empty.

Any ideas as to why?
I have tried
select * from ReceiverQueue
and
select * from SenderQueue
before I do a RECIEVE and both QUEUE's are empty

Scooter
-CODE--
USE AdventureWorks
GO

CREATE MESSAGE TYPE HelloMessage VALIDATION = NONE
GO

CREATE CONTRACT HelloContract (HelloMessage SENT BY INITIATOR)
GO

CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO

CREATE SERVICE Sender ON QUEUE SenderQueue
CREATE SERVICE Receiver ON QUEUE ReceiverQueue (HelloContract)
GO

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 = 'Hello, World';
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE HelloMessage (@.message)
COMMIT TRANSACTION
END
GO

--select * from ReceiverQueue
--select * from SenderQueue
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

Secure dialogs require a database master key. Either create a database master key, either add an WITH ENCRYPTION = OFF clause to the BEGIN DIALOG statement.

See this mini troubleshooting guide at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx

HTH,
~ Remus

|||

That was it. I knew I wasn't totally crazy!

Thanks

|||

Basically there are a lot of examples out there that worked on the the Beta 2, but don't work on the RTM: http://www.sqljunkies.com/WebLog/ktegels/archive/2006/03/08/18625.aspx

|||

Is there a way to insert a message into a SB queue without creating the Contract, Services, Sender and Reciever Queue's?

For example, I want a queue that I can send messages for a process job queue. It feels real redundant to have to send a message to a queue that sends its message to the queue. I just want a gueue that has an attached procedure I can fire.

I may just use a table with a trigger as that almost makes more sense.

Scooter

|||

The only way to send a message is with a conversation, with all wisles and bells.

You must create at least a service and a queue. The [DEFAULT] (name is case sensitive) contract and message type are always there and you can use them. In fact, you can ommit the ON CONTRACT clause of BEGIN DIALOG and the MESSAGE TYPE clause of SEND and the [DEFAULT] contract and message type will be used.

A trigger is executed in the context of the caller and the caller must wait until the trigger completes, while an activated procedure executes asynchronously, after the caller commited it's call. This is the typical reason why people are considering using activation instead or triggers. If the code to be executed by the activation/trigger is small and compact, or the caller can tolerate a long delay waiting for the trigger to complete, then triggers are simpler to deploy indeed.

What was the reason you considered activation instead of triggers in the first place?

HTH,
~ Remus

|||

Here is an example similar to what Remus described. I simplified your code snippet to kick something off in the background using one queue and one service. Btw, I also added (as comments) two END CONVERSATION calls. If you don't do these somewhere in your application you will orphan the row in the sys.conversation_endpoints table that represents the conversation you began.

-Gerald Hinson

-- code snippet -

CREATE QUEUE ReceiverQueue

GO

CREATE SERVICE Receiver ON QUEUE ReceiverQueue ([DEFAULT])

GO

DECLARE @.InitiatorConversationHandle UNIQUEIDENTIFIER;

DECLARE @.message NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @.InitiatorConversationHandle

FROM SERVICE Receiver

TO SERVICE 'Receiver';

-- Send a message on the conversation

SET @.message = 'Hello, World';

SEND ON CONVERSATION @.InitiatorConversationHandle (@.message);

COMMIT TRANSACTION

--select * from ReceiverQueue

--select * from SenderQueue

DECLARE @.TargetConversationHandle UNIQUEIDENTIFIER;

DECLARE @.MessageBody NVARCHAR(100);

BEGIN TRANSACTION;

RECEIVE @.TargetConversationHandle=conversation_handle,

@.MessageBody=message_body FROM ReceiverQueue;

print @.MessageBody

-- Don't forget to issue END CONVERSATION on the two conversation handles!!!

-- Put these where appropriate for your application

--END CONVERSATION @.InitiatorConversationHandle;

--END CONVERSATION @.TargetConversationHandle;

COMMIT TRANSACTION;

-- Cleanup

DROP SERVICE Receiver

DROP QUEUE ReceiverQueue

GO