Can we create two servicess in a same database?

Hi,

I tried creating two services in a same queue but i couldnt see them working properly. Now i got a primary doubt that can i create two services which communicate with each other in a same database? Please help me....

here is the script which i used to create the services

/************************************Scipt *********************************/

/********************** Sender Queue and Service ***********************/

CREATE QUEUE SenderQueue WITH Status = ON;

CREATE CONTRACT SenderContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE SERVICE SenderService

ON QUEUE SenderQueue(SenderContract);

/********************** Receiver Service ***********************/

ALTER PROCEDURE pArchiveMessageHandler

AS

BEGIN

DECLARE @.V_Message AS NVARCHAR(1000);

DECLARE @.V_Handle AS UNIQUEIDENTIFIER;

INSERT INTO TestServiceBroker VALUES('Starting Message', @.V_Handle);

RECEIVE TOP(1) @.V_Message = message_body,

@.V_Handle = conversation_handle

FROM ArchivingMessageQueue

INSERT INTO TestServiceBroker VALUES(@.V_Message, @.V_Handle)

END CONVERSATION @.V_Handle

WITH cleanup

END

CREATE QUEUE ReceiverQueue

WITH ACTIVATION(

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 5,

EXECUTE AS 'dbo'

);

CREATE CONTRACT RecieverContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE SERVICE ReceiverService ON QUEUE ReceiverQueue

( RecieverContract )

/********************** Sample script ***********************/

GO

DECLARE @.V_Handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.V_Handle

FROM SERVICE SenderService

TO SERVICE 'ReceiverService'

ON CONTRACT SenderContract;

SEND ON CONVERSATION @.V_Handle

MESSAGE TYPE ArchivingMessage('<XmlType/>');

select * from TestServiceBroker(nolock)

Hi:

Why do you need to create two services in the same database...in the first place. I know it works between two different databases but have not tired with same database.

Pramod

|||

Although it is possible to associate two services with the same queue, this is a very atypical usage. Messages sent to the target service as well as messages received from the target service will be delivered to the same queue.

In your script above, you are not doing this, so I'm not sure if you meant to say "same queue" or "same database".

Having both the initiator service and target service is in the same database is certainly a valid and useful configuration. You would use this typically for doing asynchronous database work.

In your script, the stored proc is RECEIVing messages from ArchivingMessageQueue (which doesn't exist) instead of ReceiverQueue. Also why are you ending the conversation with cleanup. Cleanup should not be used during normal working of an app. It is used as an admin command when things go wrong.

|||

You can have as up to ~65000 services in a database. Communication between services in the same database is identical with communication between services in different databases or different instances.

Can you specify what you mean by 'but i couldnt see them working properly' ?

A small correction to the code posted by you:

CREATE QUEUE ReceiverQueue

WITH ACTIVATION(

STATUS = ON,

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 5,

EXECUTE AS 'dbo'

);

|||

I modified the activation stored proc as well as the receiver queue based on your feedback. But still i could see the service running. When i say the services are not running means.. my query "select * from TestServiceBroker(nolock)" is not returning any rows.

I Would like to tell you people about what exactly is my requirement. We have a master table X and around 20 transaction tables say A, B, C, D, E,..etc. The transaction tables uses data available in the master table. When ever a row in the master tables is disabled(we have a flag column which holds state) we would like to disable all the trasaction tables(A, B, C, D....) data which is referring to the master X row. The process of disableing the transaction table rows can happen in an asynchronous way. To achieve this we planned to implement Fire and Forget style of service brokering. When ever a master table row is disabled we need to queue a message(using a trigger on the master table) for disabling the transaction table rows. An activation procedure needs to do the job of reading the queued request and disableing transcation table rows.

Also, can you please give me links of e-books and white papers on Service Brokers?

Regards,

Gopi

|||

Have you looked over my troubleshooting mini-guide at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx ? Please follow this guide to figure out where do your messages 'disappear'. If I'd have to guess in your case I'd say that the database is lacking the master key required for secure dialogs.

One thing to note is that you should not use the WITH CLEANUP clause in the END DIALOG statement. The CLEANUP clause removes the conversation endpoint (target) without sending any notification to the other conversation endpoint (initiator). See also this post here http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx

The design you're trying to achieve shouldn't be any problem if the asynchronous processing is required or desired. One thing you'll have to consider is the order of execution of these enable/disable operations. Service Broker guarantees the order of delivery only within a conversation.

HTH,
~ Remus

|||

Thanks Remus.

I'll debug the problem with your suggestions.

Regards,

Gopi

|||

Gopinath

I took the liberty of rewriting your sample script. I changed several things and also added numerous explanatory comments. You can compare your original to this to see what was wrong. Just copy/paste this into mgmt. studio and run the whole thing at once.

-Gerald Hinson

use master

go

drop database gopinath

go

create database gopinath

go

use gopinath

go

/********************** Sender Queue and Service (nothing else needed )***************/

CREATE QUEUE SenderQueue;

CREATE SERVICE SenderService

ON QUEUE SenderQueue;

go

/********************** RECEIVE MessageType, Contract, Queue and Service ****************/

CREATE MESSAGE TYPE ArchivingMessage;

CREATE CONTRACT ReceiverContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE QUEUE ReceiverQueue;

CREATE SERVICE ReceiverService ON QUEUE ReceiverQueue

(ReceiverContract)

go

/********************** Receiver Service ***********************/

create table TestServiceBroker (message_body nvarchar(max),

conversation_handle uniqueidentifier);

go

CREATE PROCEDURE pArchiveMessageHandler

AS

BEGIN

DECLARE @.V_Message AS NVARCHAR(1000);

DECLARE @.V_MessageTypeName AS NVARCHAR(256);

DECLARE @.V_Handle AS UNIQUEIDENTIFIER;

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION

WAITFOR (

RECEIVE TOP(1)

@.V_MessageTypeName = message_type_name,

@.V_Message = message_body,

@.V_Handle = conversation_handle

FROM ReceiverQueue), TIMEOUT 2000; -- wait 2 seconds before giving up

/* you shouldn't be inserting here unless you actually got a message back, so I added

the "if" below */

IF (@.@.rowcount > 0)

BEGIN

/* if you get and END DIALOG message, then you should END CONVERSATION as well */

IF (@.V_MessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

BEGIN

END CONVERSATION @.V_Handle;

-- WITH cleanup -- (you shouldn't have this in your app....see explanations on the forum)

END

ELSE

BEGIN

INSERT INTO TestServiceBroker VALUES(@.V_Message, @.V_Handle);

END

END -- end if

ELSE

BEGIN

break; -- timed out waiting for messages, break from loop

END

COMMIT TRANSACTION;

END -- end loop

COMMIT TRANSACTION;

END

go

/********************** Sample script to send a message***********************/

BEGIN TRANSACTION;

DECLARE @.V_Handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.V_Handle

FROM SERVICE SenderService

TO SERVICE 'ReceiverService'

ON CONTRACT ReceiverContract

WITH ENCRYPTION=OFF;

SEND ON CONVERSATION @.V_Handle

MESSAGE TYPE ArchivingMessage(N'<XmlType/>');

COMMIT TRANSACTION;

BEGIN TRANSACTION;

-- This will prevent you from seeing any error messages/replies.It would

-- be better to wait for the END DIALOG message from 'SenderService'

-- before doing this... Then you will know that your message actually got

-- processed....

END CONVERSATION @.V_Handle;

COMMIT TRANSACTION;

go

/********************** Sample script ***********************/

select * from ReceiverQueue;-- this will show that the messages are in the target queue

/**********************Turn on Activation on the receiver queue **********************/

ALTER QUEUE ReceiverQueue

WITH ACTIVATION(

STATUS=ON,

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 1,

EXECUTE AS 'dbo');

go

WAITFOR DELAY '00:00:02'; -- wait a second and then look in the activated task view to see your proc running

select * from sys.dm_broker_activated_tasks;

WAITFOR DELAY '00:00:02'; -- wait another couple of seconds and the proc should have finished running by now

go

select * from sys.dm_broker_activated_tasks;

/* look at the queue and your table again, not that the proc is done */

select * from ReceiverQueue;-- messages are gone

select * from TestServiceBroker;-- rows are now in the table

/* if the message could not be delivered for some reason, they would be in

sys.transmission_queue along with an explanation why they could not be delivered */

--select * from sys.transmission_queue

|||

Hi Gerald,

Thank you very much for you help. It's working.