'[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]'

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

You can check my BLOG here

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

You can check my BLOG here

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

You can check my BLOG here

	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

    IF @dlgId IS NOT NULL 
       AND NOT EXISTS( SELECT * FROM sys.conversation_endpoints 
                        WHERE conversation_handle = @dlgId AND state IN ('SO', 'CO') 
					)

    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)