IdleTymes Blog

Okay Ed this is the script that you wrote for the that creates a archive table for MS_Merge_History and then creates a trigger which copy all the data across from the orginal so that we have a longer achive.

I also include the trigger that fires of a email alert each time the connect fails:

CREATE TABLE [dbo].[MSmerge_history_archive] (
[agent_id] [int] NOT NULL ,
[runstatus] [int] NOT NULL ,
[start_time] [datetime] NOT NULL ,
[time] [datetime] NOT NULL ,
[duration] [int] NOT NULL ,
[comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[delivery_time] [int] NOT NULL ,
[delivery_rate] [float] NOT NULL ,
[publisher_insertcount] [int] NULL ,
[publisher_updatecount] [int] NULL ,
[publisher_deletecount] [int] NULL ,
[publisher_conflictcount] [int] NULL ,
[subscriber_insertcount] [int] NULL ,
[subscriber_updatecount] [int] NULL ,
[subscriber_deletecount] [int] NULL ,
[subscriber_conflictcount] [int] NULL ,
[error_id] [int] NOT NULL ,
[timestamp] [datetime] NOT NULL ,
[updateable_row] [bit] NOT NULL ,
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
FROM inserted

CREATE TRIGGER [Error_Alert] ON [dbo].[MSmerge_history_archive]
FOR INSERT
AS
DECLARE @Countit int
SELECT @Countit = count(*) from inserted where comments like (’%could not%’);
If @countit > 0
BEGIN
execute master.dbo.xp_cmdshell ‘C:\Components\replicationEmail.exe’;
END

Share This