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

Skip error in Transactional replication Expand / Collapse
Author
Message
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: Friday, June 27, 2014 6:12 AM
Points: 2, Visits: 146
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
GETDATE()

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.

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Thanks!
Post #1428454
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse