cannot delete subscriptions

To test SQL2000 replication, I enabled replications for NorthWind between
ServerA and ServerB. ServerA was the publisher and distributor, ServerB was
the subscriber.
After test, I deleted everything about replication on ServerA, at last, I
disabled replication. However, when I ckeck ServerB, in the
Replication\Subscriptions container, There is a "ServerA:NorthWind:" object.
I cannot remove it. If I right click this item, it only has a "set update
method..." menu which is grayed out, and another "help" menu. It looks like
a phantom object.
I then re-configure the server as a subscriber again (subscribe test DB and
set ServerA as a publisher), the Replication\Subscriptions container now has
another object "ServerA:test:test", and the content menu looks normal, which
includes "New pull subscription...", "Reinitialize", "delete"...etc. menu.
How can I delete the "ServerA:NorthWind:" object?
OK, it looks like you have lingering metadata. If you can, can you disable
replication one more time.
then run the following script
create table whack_me ([database] sysname, [table] sysname, constraint
Whack_me_pk primary key([database], [table]))
GO
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions where
publisher=@.@.servername'
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
publisher=@.@.servername'
whack_me will have a list of the lingering subscriptions and the databases
they are in. Delete the contents of these tables where the publisher
=@.@.servername
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MLi" <lige888@.gmail.com> wrote in message
news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
> To test SQL2000 replication, I enabled replications for NorthWind between
> ServerA and ServerB. ServerA was the publisher and distributor, ServerB
> was the subscriber.
> After test, I deleted everything about replication on ServerA, at last, I
> disabled replication. However, when I ckeck ServerB, in the
> Replication\Subscriptions container, There is a "ServerA:NorthWind:"
> object. I cannot remove it. If I right click this item, it only has a "set
> update method..." menu which is grayed out, and another "help" menu. It
> looks like a phantom object.
> I then re-configure the server as a subscriber again (subscribe test DB
> and set ServerA as a publisher), the Replication\Subscriptions container
> now has another object "ServerA:test:test", and the content menu looks
> normal, which includes "New pull subscription...", "Reinitialize",
> "delete"...etc. menu.
> How can I delete the "ServerA:NorthWind:" object?
>
|||I ran the script on ServerA and got a bunch of errors:
Invalid object name 'Northwind.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'model.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
....
I just want to delete everything about NorthWind replication, any easy way?
like delete all rows about NorthWind in some particular table?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
> OK, it looks like you have lingering metadata. If you can, can you disable
> replication one more time.
> then run the following script
> create table whack_me ([database] sysname, [table] sysname, constraint
> Whack_me_pk primary key([database], [table]))
> GO
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions
> where publisher=@.@.servername'
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
> publisher=@.@.servername'
> whack_me will have a list of the lingering subscriptions and the databases
> they are in. Delete the contents of these tables where the publisher
> =@.@.servername
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "MLi" <lige888@.gmail.com> wrote in message
> news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
>
|||in the table whack_me you will find a list of databases and tables. Go
through these databases and tables and delete the contents of them.
Ignore the error messages.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||sp_removedbreplication
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||With all due respect Mike the OP did this to begin with.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>
|||I'm sorry Hilary, I guess I was just too stupid to realize that. Thank you
for helping me understand my stupidity.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulLILIsEGHA.3708@.TK2MSFTNGP10.phx.gbl...
> With all due respect Mike the OP did this to begin with.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
>
|||Just found another way to delete this lingering object-- by SQL 2005
Management Studio
The object has a "delete" context menu
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>