August 25, 2007 at 10:39 am
I restored a db over another db with some replication setup (test environment!). Then I went to add a new publication, but it said the name already existed. Under replication the publication is still lsited but it will not let me delete, stating it doesn't exist. I think I should probably have removed the publication before restoring the db?
Anyway my question is - which tables do I need to remove the detail from to get past this error and let me remove that "ghost publication" from the GUI?
August 27, 2007 at 9:53 am
Looking for my notes because it wasn't as straight forward as running this stuff but it involved
sp_dropsubscription - You have to get the subscriptions removed before you can remove the pub.
sp_droppublication - I think I had to get the code for this and only run a section or two to get remove from showing.
and
sp_removedbreplication -- I may not have used any of the code out of this.
August 28, 2007 at 5:24 am
It happens to me all the time. I use this:
DECLARE @VIEW_CURSOR AS CURSOR
DECLARE @VIEW_NAME AS VARCHAR(1000)
DECLARE @STR_SQL AS VARCHAR(2000)
SET @VIEW_CURSOR = CURSOR LOCAL STATIC FOR (SELECT name FROM sysobjects where name like 'SYNC%')
OPEN @VIEW_CURSOR
FETCH FIRST FROM @VIEW_CURSOR INTO @VIEW_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR_SQL = 'DROP VIEW ' + @VIEW_NAME
EXEC (@STR_SQL)
FETCH NEXT FROM @VIEW_CURSOR INTO @VIEW_NAME
END
You may want to swap out the EXEC with a PRINT to make sure this is doing what you want before you run it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply