cannot insert duplicate key row in msrepl_transactions

We have an issue that our SQL Replication between our Application Server (holding 13 months of data) and our Reporting Server (holding 5-6 years of data).

Both systems are running on NT4, SQL Server 7 Ent SP3.

Our issue is that we used to run SQL Replication but then stopped using it, and used another method of replicating our data from the App Svr to Rpt Svr. However, we have now been told that we have to use SQL Replication.

We set it up as usual, but when the log reader starts up, it finds the 1st transaction and moves it into the distribution database, but it then fails when attempting to do the 2nd transaction with the error "Cannot insert duplicate key row in object MSrepl_transactions with unique index ucMSrepl_transactions.

We have checked the tables in the publisher db and they don't hold any duplicates, so I can only assume the key it is talking about is the key field in the distribution database, but this is a Binary field which I believe is populated by replication itself!! Is this correct?

If so, why is it trying to enter duplicates? And can we resync replication to fix it?Are you doing transactional or merge? Either way it will kick off a snapshot first to sync it. I believe you probably unchecked the initialization when you set it up. But the bottomline is that you have two server contain different data right now so the replication will not work, yet if you sync it, it will erase the historic data on your report server. My take will be to let the snapshot replicate down to a new database on your report server and load the historic data into the database(not a easy task).