FIX: Duplicate Jobs Added to the MSjobs and MSjob_commands Tables

ID: Q186001

The information in this article applies to:

Microsoft SQL Server version 6.5 Service Pack 4 and later

SQLBUG_65: 17931

SYMPTOMS

Two-phase commit replicated transactions can result in duplicate jobs being added to the MSjobs and MSjob_commands table by the LogReader task.

Two-phase commit transactions include the following:

MS DTC transactions

DB-Library two-phase commit transactions

Cross-database transactions

If the transactions are INSERT commands, the most common symptom is the distribution task failing with the following type of error:

Error : 14151, Severity: 20, State
Replication-Distribution: Task 'server_test2_server_test3' failed.
23000[Microsoft][ODBC SQL Server Driver][SQL Server]Attempt to insert
duplicate key row in object 'mytab' with unique index 'PKEY'

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

Cross-database replicated transactions are transactions that affect published tables in one database while in the context of another. For example, if you inserted rows in database 'b' while running a transaction in the context of database 'a', this would be considered a cross-database transaction.

Example


use a
go
begin transaction
insert into b.dbo.mytab values (1)
commit transaction

Internally, SQL Server processes transactions like this in database 'b' similar to the two-phase commit transactions that come from DTC or DB- Library two-phase commit.

Additional query words: sp sp52PC 2 PC prodsql

Keywords : SSrvRep kbbug6.50 kbfix6.50.SP5
Version : winnt:6.5 Service Pack 4 and later
Platform : winnt
Issue type : kbbug