sql server replication: I set up merge replication with pub and distr on the same server. The
snapshot agent fails with the following message (from Server Agent job
history): "CREATE UNIQUE INDEX terminated because a duplicate key was found
for index ID 55. Most significant primary key is 'type 24, len 16'. The
step failed." What does this mean?

TIA,
William Schmidt

I have applied SP3 and the MS site says I don't need to apply the "a"
version. I understand what the error means but I don't understand why this
happens from the snapshot agent. All the replicated tables have primary
keys (some of them are two-field keys) and guid columns in place. Is the
snapshot agent trying to create its own unique index for some reason?

Thanks for your help,
William


[quoted text, click to view]

Michael,

The history I'm looking at (EM... <DB>, Management, SQL Server Agent, Jobs,
Job History) only has the 3 steps in the snapshot job listed. The only
error message I see is the one in my original post. Is there another source
of info on this error?

Thanks,
William

P.S. See also my response to Paul Ibson

[quoted text, click to view]

It means there was a duplicate in your data and when it tried to create the
unique index, it failed. You need to go through the history to find out
what table it failed on.

--

Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

William,
This error occurs when you attempt to create a unique index and more than
one row contains the duplicate value. SQL Server has detected a uniqueness
violation and cancels the CREATE INDEX operation. There have been articles
about this sort of thing happening inadvertantly (eg
http://support.microsoft.com/default.aspx?scid=kb;EN-US;290917). These
issues are meant to have been cleared up in the service packs. Have you got
sp3(a) installed?
Regards,
Paul Ibison

can you do logging to determine exactly which table it is breaking on?

http://support.microsoft.com/default.aspx?scid=kb;EN-US;312292

By chance did you deploy this publication/subscription from a script?

[quoted text, click to view]

Thanks for the "verbose" tip. Here is the expanded error message:

[eight preceding tables returned the following two lines]
Preparing table '[dbo].[ORIENTATION]' for merge replication
*** [Article:'ORIENTATION'] Merge GUID column and triggers generation time:
221 (ms) ***
Preparing table '[dbo].[PULSE_MEASUREMENT_RESULTS]' for merge replication
SourceTypeId = 5
SourceName = ABQ_WSCHMIDT
ErrorCode = 1505
ErrorText = CREATE UNIQUE INDEX terminated because a duplicate key was found
for index ID 55. Most significant primary key is 'type 24, len 16'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 55. Most significant primary key is 'type 24, len 16'.
Disconnecting from Publisher 'ABQ_WSCHMIDT'
Microsoft SQL Server Snapshot Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: ABQ_WSCHMIDT-DAPS_DB-DAPS_DB-1

This table uses four columns for its key and has 55 columns altogether (is
this a clue). The last column is a uniqueidentifier (as is common for all
tables in this database). There are currently 624 records in this table.

I am using a stored procedure to start replication on this database. I have
to provide a GUI button to start and stop replication and I'm using stored
procedures to implement these functions. I also have one to check
replication status which is displayed on the GUI.

Thanks for your help,
William

[quoted text, click to view]

There are no indexes in the table, except for the primary key which, as I
said is composed of four columns. All the keys are unique. I verified this
(using EM) by deleting the key, saving, then recreating the key. Also,
there are no relationships or constraints defined for this table.

[quoted text, click to view]

So, for any of the unique indexes, where is the duplicate value?

--
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Ok, here is the solution...

When the snapshot agent runs, it trys to set up a unique index on the
RowGuid column. The table in question had two records with the same value
in this column -- the result of having copied the record at some point in
time (although a globally unique identifier is guaranteed to have a unique
value when created with newid() SQL will happily let you copy the value to a
new record unless the column is also set up with a constraint or index that
prevents this.)

What I did to fix the problem was to drop the RowGuid column and recreate
it -- which creates all new values using the newid() function. And from now
on, all my RowGuid columns will have a UNIQUE constraint added to their
definition.

[quoted text, click to view]