March 4, 2009 at 5:00 pm
Originally, we had setup merge replication between 2 SQL2000 computers for a single table in a single database. The publisher and distributer were on computer A and the computer B was the subscriber. Everything was working.
We migrated all databases from the two computers to new SQL2005 SP2 computers, but we did not shutdown the replication before we essentially detached and then reattached the databases on the new SQL2005 computers.
Now, we cannot rename or change the subscriber table on the new computer B. We get the error message "Cannot rename table because it is published for replication". Replication was never installed on the new SQL2005 computers.
How do I remove the left over replication pieces on the new computer B so that the table can be modified or renamed?
March 4, 2009 at 5:37 pm
If you're lucky this will fix it:
exec sp_replicationdboption
@dbname = N'AdventureWorks',
@optname = N'merge publish', -- just 'publish' for transactional
@value = N'false'
I haven't looked to see whether it cleans up master first (in which case it will proabbly fail) or cleans up the database first then master (it will do what you want).
If it doesn't fix the issue you could take apart sp_replicationdboption to see what it's doing to sys.objects and elsewhere and run the individual commands manually. I haven't looked to see how possible that is.
March 5, 2009 at 10:32 am
Use sp_removedbreplication in cases where the distributor is not available or you just need to wipe all setting out.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply