Oracle 9i -> SQL Server 2005: Violation of PRIMARY KEY constraint

Hi there,

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error message

Message
2006-06-24 12:41:59.216 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Batch send failed
2006-06-24 12:41:59.216 Category:NULL
Source: Microsoft SQL Native Client
Number: 2627
Message: Violation of PRIMARY KEY constraint 'MSHREPL_1_PK'. Cannot insert duplicate key in object 'dbo.ITEMTRANSLATION'.
2006-06-24 12:41:59.216 Category:NULL
Source:
Number: 20253

What could possibly cause this error? And how can I possibly fix it?

Best regards,

JB

Check the table in Oracle and then check it in SQL Server. Is the table definition the same? Do you have a primary key on the SQL Server side that contains fewer columns than on the Oracle side?

The error message states exactly what happened when the data was loaded into the table. That is only going to occur if you have duplicate data coming from the Oracle side. (Duplicate as defined by the primary key on the table on the SQL Server side.)

|||

The table definition is exactly the same on both the Oracle and the SQL Server side. The table itself, in SQL Server, is created by the replication engine and contains the same primary key, that's containing the same columns.

As the data is taken from a snapshot of the table at the Oracle side, where it fit's in the table, it should fit into the table in the SQL Server also. Could this error be caused by something else? I don't see why this error should/could occur...

JB

|||I don't see how it could be. If the PK on each side is the same, I also don't see why the error would even occur. You have me stumped and I don't have an Oracle instance to play with this on.|||

hi,

in the articles tab of the publication check the option that suits you

if table name tablex exist at the subscriber:

keep exisiting table unchanged
drop exisiting table and recreate it
delete data in the existing table that matches the row filter
delete data in the existing table

regards

|||i have the same problem with with Oracle 10g -> SQL Server 2005 (see Link). I use replication (merge) between SQL 2005 und SQL Express and there it works quite fine (with some exceptions). but with oracle (oracle and ms-sql tables are identical) and on some tables i get the unique constraint error with no reason. on reinitalization the tables will be droped and rebuilt but the error occours again (at the same position in the table). i got doubled entries in the ms-sql table. when i update one row table in oracle then both of the ms-sql data rows will get updatet. but the strange thing is that after reinitalization the doubled rows have identical PK's but the rest is not identical. after using this forum, google etc. i come to the conclusion that this must be a hugh bug in the replication of the ms-sql server.