Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Service Broker - Messaging Expand / Collapse
Author
Message
Posted Thursday, July 10, 2008 3:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
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
Post #531442
Posted Thursday, July 10, 2008 5:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 3,244, Visits: 5,008
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.






----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #531509
Posted Thursday, July 10, 2008 9:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
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?
Post #531748
Posted Friday, July 11, 2008 12:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 3,244, Visits: 5,008
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


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #532142
Posted Friday, July 11, 2008 2:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
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...
Post #532210
Posted Friday, July 11, 2008 2:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
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...
Post #532226
Posted Friday, July 11, 2008 5:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 3,244, Visits: 5,008
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





----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #532311
Posted Friday, July 11, 2008 6:32 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
Please show us your Sending code.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #532350
Posted Friday, July 11, 2008 8:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
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
FROM dbo.tbl_AuditDialog
WHERE tlg_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 *
FROM sys.conversation_endpoints
WHERE conversation_handle = @dlgId AND state IN ('SO', 'CO'))
BEGIN
-- ... if it isn't then delete it from our saved dialogs table ...
DELETE
FROM dbo.tbl_AuditDialog
WHERE tlg_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)
SELECT DB_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

Post #532528
Posted Friday, July 11, 2008 10:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #532650
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse