Merge Replication Conflict Notification
-
SQL2K SP4
Windows 2000 Server
I have been trying to figure this one out for quite a while now. We have multiple servers with multiple subscribers running merge replication. I would like to be notified when a subscriber synchronizes and there are conflicts. We use the default conflict resolver. I have been through the replication alerts in enterprise manager but they don't seem to be triggered when conflicts occur. I think that it is because there is nothing written to the windows event log. Can someone tell me how enable this or how to set it up or point me in the current direction. I have search the web and bol all day but have found nothing. I am currently using a store procedure and job running every hour to check the conflict tables but would like to be notified immediately so that the conflicts can be resolved.
Thank you in advance.
-
I can't think of options that require no code. You could create a trigger on each conflict table that raises an error that you either log to the event log (and)/or have a SQL Agent Alert look for. This does require code to create the triggers on each table but this could be scripted so that it is pretty much automatic. The trigger would not need to do anything except raise the error (see example below).
If you have a product that looks for entries in the event log, then this should be sufficient.
You can set up an alert so look for the message and then send off an email/net send etc.
~~~~~~~~~~~~~~~~
-- create operator "ConflictOperator" with the desired email/pager/net send options
sp_addmessage @msgnum = 50001, @severity = 15 , @msgtext = 'Merge conflict error occured on table %s', @with_log = true ,@replace = 'replace'
EXECUTE msdb.dbo.sp_add_alert @name = N'Merge conflict occurred', @message_id = 50001, @severity = 0, @enabled = 1, @delay_between_responses = 60, @notification_message = N'Test message that fires when an error 50001 occurs', @include_event_description_in = 5, @category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Merge conflict occurred', @operator_name = N'ConflictOperator', @notification_method = 4
go
create table Mytable (ccc int)
go
create trigger MyTableConflict on MyTable for Insert as
Raiserror (50001, 15, 1, 'MyTable') withlog
go
--the following insert should result in error 50001 be raised.
-- repeated running the insert does not raise the alert to fire each time because @delay_between_responses is set to 60 (seconds)
insert into mytable values (1)
-
Hi Iain
the most obvious thing would be creating a performance alert on the counter "Merge conflicts/sec". But from own experience I can tell you it won't work.
My solution was running a job every 5 minutes which executes the following script:
If(
SELECT SUM (Rows) sysindexes i
JOIN sysobjects o
ON i.id = o.id
WHERE o.xtype = 'U' AND o.name LIKE 'conflict%'
AND i.indid < 2 ) > 0
BEGIN EXEC xp_sendmail @recipient = 'administrator@nwtraders.com',
@subject = 'A conflict has occured in ReplDB'
END
Of course this will only work if you have SQLMail enabled.
The problem with the previous solution is that you need triggers on the conflict tables and if I remember it right, the system doesn't allow this because it sees the conflict tables as system tables. I have no time to test it, but if I'm wrong you can try that as well.
M
[font="Verdana"]Markus Bohse[/font]
-
Yes the conflict tables are system tables but you are able to create triggers on them. You are expected to be allowed to perform most things on these tables otherwise how can you write your own conflict resolver.
You do need to be able to ensure that the trigger exists on every conflict table. You should be able to script this fairly easily. Something like the following could do the job
Declare @tab varchar (128)
Declare @tabTrigger varchar (128)
Declare @sql varchar (1000)
Declare Tables Cursor FOr SELECT Conflict_Table
FROM sysmergearticles
Open Tables
Fetch Next From Tables into @tab
While @@Fetch_status = 0
Begin
Set @tabTrigger = @tab + 'ConflictTrigger'
If Not Exists (Select 1 From Sysobjects where name = @tabTrigger and xtype = 'tr')
Begin
Set @sql = 'Create Trigger [' + @tabTrigger + '] On [' + @tab + '] for insert as
Raiserror (15001, 15, 1) with log'
Exec (@SQL)
End
Fetch NExt From Tables into @tab
End
close tables
deallocate tables
-
Reviving an old thread...
Does anyone know if this (merge replication conflict notification) is possible without code in SQL Server 2005? This will affect our business decision of how soon to upgrade to Yukon.
Thanks!
Nate
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply