I need some assistance with getting service broker to work across servers. I have 2 separate servers and I am trying to send a message from one server to the other. No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server. Where is the message going?
Here is the code I use to create the service broker objects that are being used…
RUN THIS ON THE SENDING SERVER:
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE
CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE
GO
CREATE CONTRACT [MyContract] (
MyMessage SENT BY INITIATOR,
MyResponse SENT BY TARGET)
GO
CREATE QUEUE [MyInitiatorQueue] with status = ON
CREATE QUEUE [MyTargetQueue] with status = ON
GO
CREATE SERVICE [MyInitiatorService] ON QUEUE [MyInitiatorQueue]
GO
CREATE ROUTE [RouteToODS]
WITH
SERVICE_NAME = N'MyTargetService',
BROKER_INSTANCE = '1BB213E2-67A7-4059-BAF8-D9B5F31E358E',
ADDRESS = N'TCP://CONSULT01:4022'
GO
CREATE ENDPOINT DWHEndPoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED)
GO
RUN THIS ON THE RECEIVING SERVER:
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE
CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE
GO
CREATE CONTRACT [MyContract] (
MyMessage SENT BY INITIATOR,
MyResponse SENT BY TARGET)
GO
CREATE QUEUE [MyInitiatorQueue] with status = ON
CREATE QUEUE [MyTargetQueue] with status = ON
GO
CREATE SERVICE [MyTargetService] ON QUEUE [MyTargetQueue] ([MyContract])
GO
CREATE ROUTE [RouteToDWH]
WITH
SERVICE_NAME = N'MyInitiatorService',
BROKER_INSTANCE = 'F0BF4E80-704E-4CFE-80FC-637A1EC128C5',
ADDRESS = N'TCP://DWH:4022'
GO
CREATE ENDPOINT ODSEndPoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED)
GO
SEND A MESSAGE USING THE FOLLOWING:
Declare @.ConversationHandle uniqueidentifier
Begin Transaction
Begin Dialog @.ConversationHandle
From Service [MyInitiatorService]
To Service 'MyTargetService'
On Contract [MyContract]
With Encryption = Off,
Lifetime = 600;
Send on Conversation @.ConversationHandle
Message Type [MyMessage] (N'This is a my message')
End Conversation @.ConversationHandle
Commit
Select GET_TRANSMISSION_STATUS(@.ConversationHandle)
I've done a bit more in trying to resolve the issue and I'm more confused than ever...
As per the Service Broker Routing article on MSDN I have removed the route from the database of the target server and created it in MSDB instead. After doing that the message now remains in the transmission queue of the sending server but without any error (transmission_status is blank). However when I look at the summary report of Service Broker on the sending server under #Messages in Transmission Queue it shows 2 messages and “MyInitiatorService(Non existent service)” in the key to the graph. If there was a problem with the service why would it not show the error in the transmission queue, and what is the problem with the service?
|||I don't know what routing article you mention, but you should definitely have a route both on the sending side as well as on the receiving side - in the databases, not in MSDB.
Also, take out the END CONVERSATION after you have send. End conversation kills the conversation and discards un-send messages. That's probably why you don't see a message in the sys.transmission_queue.
Niels|||
Most likely your message reaches the target, it gets denied access on the service (since you use unsecure dialogs and you don't grant SEND permission to [Public]), the error comes back and then the dialog gets deleted, since you already ended it.
1. Grant SEND permission to [Public] on the target service
2. Don't end the dialog on the initiator side prematurely
HTH,
~ Remus
|||
BTW, you could try following the steps from this article to identify the problem: http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx
HTH,
~ Remus
|||
Niels, End conversation does not discard pending messages. However, once you have closed the conversation on your end, you will no longer be able to receive response messages, including errors sent back by target (eg> sender does not have permission to send to this target service).
Flamin, therefore you should not end conversation at the initiator until you've confirmed that it has been delivered to the target service.
Rushi
|||
Thanks for the clarification on End Conversation, I didn’t know that ending the conversation would block errors too. As soon as I removed the End Conversation I received a permissions error. As you suspected Remus the problem was due to a lack of send permissions on the service.
|||
Hi Remus,
I am facing problem which is somewhat related to this it will be great if you can help me in resolving it. Actually for me every thing is working fine in the sense i am able to send and receive the messages.
But i see their are so many messages which are stuck up in sys.conversation_endpoints table. All this messages are having state as DI and initiator as 0.
As i am sending number of messages using same queue i am not able to get to the route of the cause due to which messages are getting stuck up.
Even i am ending conversation on initiator end so can that be the reason for the issue.
Please let me know if you need any more information from me.
Thanks
|||
Those are not messages, but conversation endpoints. DI state for is_initiator = 0 means that you receive EndDialog message on the target, but you don't end the target endpoint of the dialog. To completely close, a conversation must be ended by both conversation endpoints (initiator and target). Typicaly this means than wehn the RECEIVE returns a message of type [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] you should issue END CONVERSATION on the received handle.
HTH,
~ Remus
|||
Thanks for your suggestions
But the real issue is when and how should i delete those records from target end.
This is how i am sending message
SELECT @.SendService = '//'+@.Pod_ID+'/TransactionQueue',
@.ReceiveService = '//ODS/'+@.Pod_ID+'/TransactionQueue',
@.Contract = '//Contract/TransactionQueue';
BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract;
SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);
END CONVERSATION @.handle;
And this is how i am processing the message on target end
RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
I am having a code
IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
RETURN 0
END
Which i thought is creating problem. But i see their are no records with this message type in TransactionQueue (user defined queue) and sys.transmission_queue (system queue).
I am still curious to know how can i get rid of those records from conversation_endpoints. As the count is growing like anything.
Just one more question. If solution is ending conversation on target end, which approach should i follow
1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;
2: IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END
TIA
Prashant
|||
In case 1, you are ending the dialog regardless of the type of message received. If you expect more than one message interaction, there will be no way to receive more messages.
In case 2, you are ending the dialog only when the initiator has closed the dialog and you know for sure, no more messages are expected.
Also, let me warn you that the pattern you are using (which we call fire-and-forget), where the initiator begins a dialog, sends a message and ends the conversation can be problematic for two main reasons:
i) There is no app-level reliability, i.e. without getting a response back, the initiating app will never know whether its message was processed or not.
ii) During development, you will not be able to see error messages sent back by the target.
|||
 Prashant wrote: |
|
1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;
|
Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.
| Prashant wrote: |
|
IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END
|
Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.
|||
| Prashant wrote: |
|
1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;
|
Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.
| Prashant wrote: |
|
IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END
|
Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.
|||
| Prashant wrote: |
|
1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;
|
Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.
| Prashant wrote: |
|
IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END
|
Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.
|||
| Prashant wrote: |
|
1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;
|
Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.
| Prashant wrote: |
|
IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END
|
Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.
Prashant wrote: