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
Declare @PublisherDB sysname,
-- 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
Where publisher_db = @PublisherDB
-- Get the command
@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.