SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Skip error in Transactional replication


Skip error in Transactional replication

Author
Message
boris.kovalcik
boris.kovalcik
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
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.
juliaer
juliaer
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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!
Mr Jakeer
Mr Jakeer
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 263
please refer the below link, i am able to resolve the issue,

http://www.sqlideas.com/2015/05/skip-distributor-agent-error-in-sql.html

--Publisher
USE distribution
GO

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
@PublisherServer,
@PublicationDB,
@PublicationName,
@SubscriberServer,
@SubscriberDB

GO

--On Subscriber

USE SubscriberDB
GO

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

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

EXEC Sp_setsubscriptionxactseqno
@PublisherServer,
@PublicationDB,
@PublicationName,
0x00000022000000C1000400000000

GO

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

Regards,
Syed Jakeer,
----Only SQL Server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search