FIX: Duplicate Jobs Added to the MSjobs and MSjob_commands Tables (186001)


The information in this article applies to:

  • Microsoft SQL Server 6.5 Service Pack 4 and later
This article was previously published under Q186001
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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

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.
Modification Type:MajorLast Reviewed:3/14/2006
Keywords:kbBug kbfix KB186001
©2004 Microsoft Corporation. All rights reserved.