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
