Merge replication triggers use the wrong views after deleting publication

Question

So, we had 1 merge publication on our database.  When creating a merge publication, 3 triggers are created for each table (insert, update, and delete), and also a view is created per replicated table which these triggers use to reference the table.

Next, we created a second publication, which created a brand new set of views, with different names, and also updated the triggers to use those new names. 

When we deleted the second publication (because we didn't need it) the new views got dropped, but the triggers continue to use their names, instead of reverting back to the old names, which is causing errors.

Has anyone seen this before?

---

Clint

Answer 1

Yes, I've had to snag existing copies of the views from other valid subscriptions.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

How did you do that?  SSMS won't let me script them out?

---

Clint

Answer 3

Just FYI, I found a fix to the problem.  It wasn't the addition of the second publication that caused the problem, it was the fact that the second publication was set to pre-compute data partitions.  When this happens, the system views with "...repl_view..." in the name are recreated, and the triggers actually begin to access them.  Then, if you drop that second publication, the triggers still continue to access those views which are dropped when you drop the publication

If you just set that second publication to not use pre-computed partitions will "fix" your triggers, then you can drop it.

---

Clint

Answer 4

use sp_helptext or object_definition to get the view code.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941