SQL Server generating error during merge replication on a table which has been deleted

Question

I added a table tblJoinCustBlastList to a merge replicated database and now I'm getting this failure error during replication:

Error messages:

The schema script 'if object_id(N'[dbo].[tblJoinCustBlastlist]') is not null exec('ALTER TABLE [dbo].[tblJoinCustBlastlist] ADD CONSTRAINT PK_tblJoinCustBlastlist PRIMARY KEY CLUSTERED ( JoinID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

')' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 Table 'tblJoinCustBlastlist' already has a primary key defined on it. (Source: MSSQLServer, Error number: 1779) Get help: http://help/1779 Could not create constraint. See previous errors. (Source: MSSQLServer, Error number: 1750) Get help: http://help/1750

All of the help links are dead. The table which caused the replication to fail (rblJoinCustBlastList) only has 3 fields:

JoinID -int- Primary Key - no nulls

fkCustID - int- no nulls

fkBlastListID - int- no nulls

and the system-created field Rowguid - uniqueidentifier

For my own troubleshooting, I removed this table from the articles to be replicated and then I even deleted the entire table from the database. When I tried to synch again to see if the error cleared out, I got the exact same message! Why is it giving me an error on a table that's not even supposed to be replicating, and is even deleted? And how do I fix this? Please help a newbie, none of my users can synch now...

Answer 1

This isn't the perfect answer to the question but easiest solution is likely to start with a new snapshot and push that out to the hosts.

When you have a lot of replication hosts I have found that Merge oftentimes does a Round Robin so changes adding / remove from servers would go round in a circle for weeks if you didn't start with a new snapshot when you made a schema change.

The errors are

MSSQL_REPL-2147201001 - Failure to create directories for temporary snapshot files 1779 - is the message when you try and add a primary key to a table that has one 1750 - is the message when you try and fail to create a constraint

Answer 2

I fixed it by removing the table tbljoincustblastlist from the subscriber. I had removed this table from the publisher, but not from the subscriber and it was causing an error somehow, I presume it had something to do with a primary key constraint. whew!