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...
>

cannot delete publication

We are using SQL Server 2005 Service Pack 2 on WIn2003.
We lost our distributor and I reinstall SQL Server in Distributor machine.
Now I am not able to delete publication and subscription from publisher and
subscriber.
Any help highly appreciated.
tried
sp_removedbreplication
Msg 3724, Level 16, State 2, Procedure sp_MSdrop_peertopeer_tables, Line 27
Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for
replication.
Thanks
Or I get following error
OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned
message "Communication link failure".
Msg 233, Level 16, State 1, Line 0
Named Pipes Provider: No process is on the other end of the pipe.
"Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
news:%23t%23jvCv0HHA.4236@.TK2MSFTNGP06.phx.gbl...
> We are using SQL Server 2005 Service Pack 2 on WIn2003.
> We lost our distributor and I reinstall SQL Server in Distributor machine.
> Now I am not able to delete publication and subscription from publisher
and
> subscriber.
> Any help highly appreciated.
>
> tried
> sp_removedbreplication
> Msg 3724, Level 16, State 2, Procedure sp_MSdrop_peertopeer_tables, Line
27
> Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for
> replication.
>
> Thanks
>
|||set the ignore_distributor =1 parameter
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
"Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
news:%234MijUv0HHA.4004@.TK2MSFTNGP05.phx.gbl...
> Or I get following error
> OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned
> message "Communication link failure".
> Msg 233, Level 16, State 1, Line 0
> Named Pipes Provider: No process is on the other end of the pipe.
>
>
>
>
> "Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
> news:%23t%23jvCv0HHA.4236@.TK2MSFTNGP06.phx.gbl...
> and
> 27
>
|||I did put this parameter yesterday, but Now when I refresh 'Local
Publication' I get ' Failed to retrive data for this request.'
More info
A exception occurred while executing a transact- sql statement or batch
Lock request time out period exceeded
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OPzWNA30HHA.4184@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> set the ignore_distributor =1 parameter
> --
> 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
> "Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
> news:%234MijUv0HHA.4004@.TK2MSFTNGP05.phx.gbl...
Line
>
|||You are getting some deadlocking occurring. Run dbcc opentran and see if
there are open transaction. evaluate whether you can kill them. Close down
all instance of EM, stop the agents and try again.
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
"Sandeep Shankar" <sandeeps@.dntg.net> wrote in message
news:ez9U3y40HHA.4652@.TK2MSFTNGP05.phx.gbl...
>I did put this parameter yesterday, but Now when I refresh 'Local
> Publication' I get ' Failed to retrive data for this request.'
> More info
> A exception occurred while executing a transact- sql statement or batch
> Lock request time out period exceeded
> Thanks
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OPzWNA30HHA.4184@.TK2MSFTNGP06.phx.gbl...
> Line
>

Cannot create subscriber on MSDE2000

I am very new at SQL replication. I set up a publication and distributor at
our SQL 2000 server. All went fine. Now I am trying to create a laptop
subscriber to use (I think) an anonymous subscription. I setup the laptop
with MSDE 2000 SP3. I now want to create a subscription while connected to
the network. I want to use the Windows Syncronizer to synch the database.
I cannot find out how to create the initial subscription database on the
laptop. When I try to manually configure the Windows Synchronizer, I get an
error "The process could not access database 'MCFIData' on server 'LAPTOP1'.
Database is invalid or cannot be accessed"
Well DUH...I was told that it would be created if it does not exist. Any
help would be appreciated. Thanks.
David
Ignore this. I did not have the SQL Agent running on the laptop with MSDE
2000 SP3. I'm not sure why, but there was no prompt for this option when I
installed MSDE. Also there was no instruction to do so on the documentation
for setting up MSDE to be a subsriber!
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:elqk$rE%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I am very new at SQL replication. I set up a publication and distributor
>at our SQL 2000 server. All went fine. Now I am trying to create a laptop
>subscriber to use (I think) an anonymous subscription. I setup the laptop
>with MSDE 2000 SP3. I now want to create a subscription while connected to
>the network. I want to use the Windows Syncronizer to synch the database.
>I cannot find out how to create the initial subscription database on the
>laptop. When I try to manually configure the Windows Synchronizer, I get
>an error "The process could not access database 'MCFIData' on server
>'LAPTOP1'. Database is invalid or cannot be accessed"
> Well DUH...I was told that it would be created if it does not exist. Any
> help would be appreciated. Thanks.
> David
>

Cannot connect to remote distributor -- linked server error

I have 2 servers: #1 -- SQL 2005 SP1 publisher ; #2 -- SQL 2005 SP2 subscriber

originally I had #1 as pub and dist but dist but killing my CPU so I was in the process of moving the dist to #2... Got it all configured and when I tried to add #2 as a dist for #1 it fails when I get to the administrative password screen... It give me an error about how it cannot connect with the given password but under that is says linked server failed.

Since it mentioned linked server I tried to connect via the previous linked server to #2 and it failed... I cannot connect to #2 anymore from #1. I can login directly to #2 and I can use osql to connect to #2 but linked server does not work. I tried all drivers and many configurations...

Any ideas?

Some updates...

I can put the distributor locally on #1 and create both push and pull subscriptions to #2.