July 10, 2008 at 3:21 am
All,
Been trying to setup service broker as an async way of doing data level auditing. This is from one server to another. Both are SQL2005 sp2.
This is the error:
This message could not be delivered because the conversation ID could not be associated with an active conversation. The message origin is: 'Transport'.
I am using a very simple message (to get this to work, I had to change <> to []):
'[AuditMsg]
[SourceTable]' + 'jct_am_ActivityActivity' + '[/SourceTable]
[LinkID]' + '20' + '[/LinkID]
[ChangeType]' + 'UPDATE' + '[/ChangeType]
[ChangeSource]' + 'IRIS' + '[/ChangeSource]
[ChangeDatetime]' + '20 July 2008' + '[/ChangeDatetime]
[ChangeUsername]' + 'LIVE01\' + '[/ChangeUsername]
[OLDValue]' + 'TESTZ|TEST' + '[/OLDValue]
[HostName]' + 'SERVER077' + '[/HostName]
[/AuditMsg]'
If I run this message against the procedure that the QUEUE is supposed to use, it successfully inserts the record.
However when I fire from SOURCE server it does not seem to reach the procedure.
On the SOURCE I have the following in PROFILER (EventSubClass):
1 - Create
11 - BEGIN DIALOG
1 - SEND Messaeg
2 - Remote
2 - Remote
2 - Connected
1 - Message with Acknowledgement Sent
1 - Message with Acknowledgement Sent
Looks like it sends the message again
On the TARGET I have the following in PROFILER (EventSubClass):
6 - Accept
1 - Login Success
1 - Local
1 - Create
12 - Dialog created
6 - Received Sequenced Message
3 - Message with Acknowledgement Received
1 - Start
2 - End
1 - Local
2 - Acknowledgement Sent
Then it seems to receive duplicate message(s):
1 - Local
2 - Unsequenced Message
I cannot see the Acknowledgements being received on the SOURCE server. Would this stop the message from hitting the QUEUE on the TARGET?
So what do I need to do to fix this?
Please ask if more info is needed, no errors in SQL logs or in the Windows event logs.
Thanks in advance,
Graham
July 10, 2008 at 5:52 am
Check the routes exists;
In Source to Destination
And
In Destination to Source
The route should be defined at both ends, Source and Destination. Otherwise, the conversation will not take place.
Also, go for IP:port_number based route instead of TRANSPORT type.
July 10, 2008 at 9:48 am
Here are my routes:
Source
CREATE ROUTE RouteDataSender
AUTHORIZATION dbo
WITH
SERVICE_NAME = '//Audit/AcMan_DataWriter',
BROKER_INSTANCE = '51E3A8D4-BE8B-4162-8B90-245E321F674A',
ADDRESS = 'TCP://10.0.x.x:x'
GO
Target:
CREATE ROUTE [RouteDataReceiver]
AUTHORIZATION dbo
WITH
SERVICE_NAME = '[tcp://10.0.x.x:x/db_ActivityManagement/Audit/DataSender]',
BROKER_INSTANCE = 'AE6697F2-83BA-48FF-93CF-DC8C7BA40FFA',
ADDRESS = 'TCP://10.0.y.y:y'
GO
Have I done something with these?
July 11, 2008 at 12:08 am
Well i dont see any problem in the Routes. Just check the following;
1. Grant Connect to Endpoints to [public] at both sender and Receiver.
2. Grant SEND to Service to [public] at both sender and Receiver.
3. Grant Control on Service to Public on Receiver
4. Alter database of sender and receiver to Set Trustworthy ON on sender and receiver.
Also, Empty the sys.Conversation_endpoints DM and transmission_queue by using END CONVERSATION ... WITH CLEANUP. and try again with the empty transmission queue.
Good Luck.
Atif Sheikh
July 11, 2008 at 2:24 am
Tried all that and still the same thing!
How does the messaging work?
When the intial message is sent, is it sent to the initiator queue or the target queue?
If it is the target, must the the acknowledgement (that the target sends to the initiator) be received/accepted by the initiator, before the message will hit the actual queue?
That message is not making it back to the initiator and then the initiator re-sends, so I am thinking that is where my issue is...
July 11, 2008 at 2:59 am
Using a packet sniffer, it seems that I may have the ports setup incorrectly.
Packet going from source to target:
SrcPort - 4509
DstPort - 4022
Packet going from target to source:
SrcPort - 4022
DstPort - 4509
This kind of makes sense, as the service broker on the source server is listening on port 4022. But where is this set? Seems to change on ever conversation I setup...
July 11, 2008 at 5:41 am
Ports are mentioned in Endpoints and then in routes.
Try using same port numbers at sender and receiver, say, 9998.
My Service Broker is working perfectly on this port.
I have never tried even my self on different port numbers at sender and receiver.
Atif Sheikh
July 11, 2008 at 6:32 am
Please show us your Sending code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 11, 2008 at 8:53 am
Both routes specify the port as 4022. So not sure why the one appears as it does.
I have setup Service Broker on another instance of SQL going between 2 databases and it works perfectly. So I am now retro fitting the works bits back into the cross server version.
here is my procedure that I use to send the code:
ALTER PROCEDURE [dbo].[usp_AuditSendData_f]
(
@AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @dlgId UNIQUEIDENTIFIER
-- Check if our database already has a dialog id that was previously used
-- if it does reuse the conversation
SELECT@dlgId = tlg_dialogID
FROMdbo.tbl_AuditDialog
WHEREtlg_dbID = DB_ID()
-- if we're reusing a dialog conversation then
-- check if it is in a good state for conversation ...
IF @dlgId IS NOT NULL
AND NOT EXISTS(SELECT*
FROMsys.conversation_endpoints
WHEREconversation_handle = @dlgId AND state IN ('SO', 'CO'))
BEGIN
-- ... if it isn't then delete it from our saved dialogs table ...
DELETE
FROMdbo.tbl_AuditDialog
WHEREtlg_dbID = DB_ID()
AND tlg_dialogID = @dlgId
-- ... optionally you can end the conversation here,
-- but it is better to end it from target server
-- END CONVERSATION @dlgId WITH CLEANUP
-- ... and set it to null to create a new dialog
SELECT@dlgId = NULL
END
IF @dlgId IS NULL
BEGIN
-- Begin the dialog, with the new Id
BEGIN DIALOG CONVERSATION @dlgId
FROM SERVICE [tcp://10.0.0.89:4022/db_ActivityManagement/Audit/DataSender]
TO SERVICE '//Audit/AcMan_DataWriter',
-- this is a MasterAuditDatabase Service Broker Id
-- (change it to yours and remove )
'51E3A8D4-BE8B-4162-8B90-245E321F674A'
ON CONTRACT [//Audit/AcMan_Contract]
WITH ENCRYPTION = OFF;
-- add our db's dialog to AuditDialogs table if it doesn't exist yet
INSERT INTO dbo.tbl_AuditDialog(tlg_dbID, tlg_dialogID)
SELECTDB_ID(), @dlgId
END
-- Send our data to be audited
;SEND ON CONVERSATION @dlgId
MESSAGE TYPE [//Audit/AcMan_rMessage] (@AuditedData)
END TRY
BEGIN CATCH
INSERT INTO dbo.tbl_AuditError
(
aer_ErrorProcedure
,aer_ErrorLine
,aer_ErrorNumber
,aer_ErrorMessage
,aer_ErrorSeverity
,aer_ErrorState
,aer_AuditedData
)
SELECT ERROR_PROCEDURE()
,ERROR_LINE()
,ERROR_NUMBER()
,ERROR_MESSAGE()
,ERROR_SEVERITY()
,ERROR_STATE()
,@AuditedData
END CATCH
END
July 11, 2008 at 10:17 am
You have this check for your re-used conversation handle being either NULL, non-existent or bad state:
IF @dlgId IS NOT NULL
AND NOT EXISTS( SELECT * FROM sys.conversation_endpoints
WHERE conversation_handle = @dlgId AND state IN ('SO', 'CO')
)
But it appears that you only handle the NULL case:
IF @dlgId IS NULL
BEGIN -- Begin the dialog, with the new Id
BEGIN DIALOG CONVERSATION @dlgId
FROM SERVICE [tcp://10.0.0.89:4022/db_ActivityManagement/Audit/DataSender]
TO SERVICE '//Audit/AcMan_DataWriter',
-- this is a MasterAuditDatabase Service Broker Id
-- (change it to yours and remove
'51E3A8D4-BE8B-4162-8B90-245E321F674A'
ON CONTRACT [//Audit/AcMan_Contract]
WITH ENCRYPTION = OFF;
-- add our db's dialog to AuditDialogs table if it doesn't exist yet
INSERT INTO dbo.tbl_AuditDialog(tlg_dbID, tlg_dialogID)
SELECT DB_ID(), @dlgId
END
-- Send our data to be audited
;SEND ON CONVERSATION @dlgId
MESSAGE TYPE [//Audit/AcMan_rMessage] (@AuditedData)
Note that if it is non-existent or in a bad state, nothing is done, but the SEND still runs and tries to use it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 14, 2008 at 4:31 am
If it is non-existant, it starts a new conversation, new GUID, which then gets inserted into the dialog table. Admittedly, there is nothing to catch "bad" conversations. I also clean up all the dialog tables and conversation tables whenever I re-try.
This all works in the single instances across multiple databases environment.
So I am now re-starting from scratch. Restored the source database, dropped and re-created the target database.
Using exactly what I had for the same instance version, I am now trying to change the services and the routes, server bindings, endpoints, etc.
For future reference, I found an interesting way of checking the target queues, rename the procedure that is supposed run on the target queue. This then leaves the data in the queue, and you can manual run the procedure once the data is in the queue... Helped me a lot on the single instance setup 🙂
July 14, 2008 at 5:44 am
Ok, I have gotten the messaging to work (across servers), but I have one final issue.
The acknowledgement message is not working going from the TARGET to the SOURCE correctly. In profile on the TARGET it processes a task for Acknowledgement Sent, but nothing is received on the SOURCE side. This then cause the SOURCE to re-send the message, which the TARGET does not accept, as it has already accepted and processes that message.
I am going break out the packet sniffer again.........
Will post the simplified code once I have gotten it all to work 🙂
July 14, 2008 at 8:54 am
graham cleverly (7/14/2008)
If it is non-existant, it starts a new conversation, new GUID, which then gets inserted into the dialog table. Admittedly, there is nothing to catch "bad" conversations. I also clean up all the dialog tables and conversation tables whenever I re-try.
No, look at it again. It only does that if it is NULL. If it is non-existant or in a bad state, it falls through to the SEND.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 15, 2008 at 7:53 am
rbarryyoung (7/14/2008)
graham cleverly (7/14/2008)
If it is non-existant, it starts a new conversation, new GUID, which then gets inserted into the dialog table. Admittedly, there is nothing to catch "bad" conversations. I also clean up all the dialog tables and conversation tables whenever I re-try.No, look at it again. It only does that if it is NULL. If it is non-existant or in a bad state, it falls through to the SEND.
I accept that if the conversation is in a bad state I will have issues. However if its non-existant? When compared to the dialog table?
Still struggling with this return message, admittedly not spent much time on it. Where is the first place to start looking? The route from TARGET to SOURCE?
July 18, 2008 at 5:21 pm
This is the only clear reference that I can find to this specific error message and how to debug it:http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=356938&SiteID=1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply