Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Skip error in Transactional replication Expand / Collapse
Posted Wednesday, September 19, 2012 7:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 25, 2015 2:44 AM
Points: 2, Visits: 153
Hi all,

I have confusion here with replication. I've made some tests and here are results:
I need to troubleshoot environment where cross replication solution is implemented. Don't ask me why, customer wants it.
I made tests on transactional replication, not cross but I don't thing it is different in this case.
I've created replication between two servers on one table with two columns lets say ID column(identity with increment 1) and timestamp column. I've created job, that every ten seconds inserts

value into table and this is replicated to another server.
On subscriber table I have manually inserted row with higher ID as as last identity on publisher and have waited for error. when I did not make changes on Replication agent provile, replication failed with error 2627(Violation of PRIMARY KEY constraint 'PK_tab1'. Cannot insert duplicate key in object 'dbo.tab1'.) replication gives error and stopped to work while retrying to insert duplicate key. After this I was able to check transaction details because I see xact_seqno in MSrepl_errors table and commandID so that I could track failed command.
Later I've decided to skip this kind of errors by changing Agent profile, that will omit this kind of errors. But after this change, if some duplicate key issue occurs I see xact_seqno like 0x0000000000000000000000000000 and commandid is also 0 and when i use these result in this script
Use distribution;

Declare @PublisherDB sysname,
@PublisherDBID int,
@SeqNo nchar(22),
@CommandID int

-- Set publisher database name and values from Replication Monitor
Set @PublisherDB = N'testDB'
Set @SeqNo = N'0x0000000000000000000000000000'
Set @CommandID = 0

-- Find the publisher database ID
Select @PublisherDBID = id
From dbo.MSpublisher_databases
Where publisher_db = @PublisherDB

-- Get the command
Exec sp_browsereplcmds
@xact_seqno_start = @SeqNo,
@xact_seqno_end = @SeqNo,
@command_id = @CommandID,

I see empty result.
My question is, if this is standard behaviour of SQL server or not, and if yes, is there any way of how to get details of skipped transactions that was causing 2627 code errors.
Thanks for any responses.

Post #1361340
Posted Friday, March 8, 2013 2:22 AM


Group: General Forum Members
Last Login: Monday, June 23, 2014 6:01 AM
Points: 11, Visits: 110
Hi... I have the same problem rigth now.
I decided to skip that error, but I would like to know what transaction do that...
Any response will be appreciate!
Any way I'm looking for a solution

Post #1428454
Posted Wednesday, March 9, 2016 4:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:48 AM
Points: 3, Visits: 221
please refer the below link, i am able to resolve the issue,

USE distribution

DECLARE @PublisherServer VARCHAR(50),
@PublicationDB VARCHAR(50),
@SubscriberServer VARCHAR(50),
@SubscriberDB VARCHAR(50),
@PublicationName VARCHAR(50)

SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test'
SET @SubscriberServer = '<Subscriber>'
SET @SubscriberDB = 'test1'
SET @PublicationName = 'testpub'

EXEC Sp_helpsubscriptionerrors


--On Subscriber

USE SubscriberDB

DECLARE @PublisherServer VARCHAR(50),
@PublicationDB VARCHAR(50),
@PublicationName VARCHAR(50)

SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test'
SET @PublicationName = 'testpub'

EXEC Sp_setsubscriptionxactseqno



Syed Jakeer,
----Only SQL Server

Post #1768086
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse