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 connect to subscriber

Setup:
- SQL2000 SP3 running on Windows2000 servers.
I have a merge replication running between three identical servers. Two of
the servers, the publisher and subscriber1, receive data from different
sources and are synced every day at 4am. Subscriber2 is a query server only
and is updated by the publisher at 5am.
Replication has been running flawlessly since we upgraded everything nearly
a year ago. This morning the merge agent for Subscriber1 failed with the
following error (XXXX is the server name):
"The process could not connect to Subscriber 'XXXX'.
(Source: XXXX (Agent); Error number: 20084)"
"Cannot generate SSPI context
(Source: XXXX (Data source); Error number: 11004)"
Subscriber2 succeeded. Nothing has changed on the servers, and the logon
credential are the same for each.
I'm at a loss here. Anyone have any idea what might be happening and how to
fix it?
Thanks,
John Steen
There are some patches which seem to be causing this problem. The first
thing I would do is try to connect to the problem machine using SQL
Authentication. This might indicate that someone has changed the server from
mixed authentication to windows. Then try Windows - do you get the SSPI
error there too?
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
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:ABA30CE3-CA41-481E-9195-35482227D318@.microsoft.com...
> Setup:
> - SQL2000 SP3 running on Windows2000 servers.
> I have a merge replication running between three identical servers. Two
of
> the servers, the publisher and subscriber1, receive data from different
> sources and are synced every day at 4am. Subscriber2 is a query server
only
> and is updated by the publisher at 5am.
> Replication has been running flawlessly since we upgraded everything
nearly
> a year ago. This morning the merge agent for Subscriber1 failed with the
> following error (XXXX is the server name):
> "The process could not connect to Subscriber 'XXXX'.
> (Source: XXXX (Agent); Error number: 20084)"
> "Cannot generate SSPI context
> (Source: XXXX (Data source); Error number: 11004)"
> Subscriber2 succeeded. Nothing has changed on the servers, and the logon
> credential are the same for each.
> I'm at a loss here. Anyone have any idea what might be happening and how
to
> fix it?
> Thanks,
> John Steen
>
|||Thanks for getting back to me so quickly, Hilary.
All three servers are using mixed authentication and identical credentials,
and there have been no patches or updates applied. I ran the merge agent for
Subscriber1 this morning manually and it succeeded. I did the same thing for
Subscriber2 but it failed. A second attempt succeeded. So it doesn't seem
to be an authentication issue.
We have a BU Exec SQL agent that does a differential backup at the same time
the merge agents run, so I'm going to to change the time that the agents run
to see if there's a conflict of some sort. If not, then I'll push it off to
our telecom people.
Thanks,
John
John Steen
remove (nospam) from e-mail address to send me a message
"Hilary Cotter" wrote:

> There are some patches which seem to be causing this problem. The first
> thing I would do is try to connect to the problem machine using SQL
> Authentication. This might indicate that someone has changed the server from
> mixed authentication to windows. Then try Windows - do you get the SSPI
> error there too?
> --
> 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
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:ABA30CE3-CA41-481E-9195-35482227D318@.microsoft.com...
> of
> only
> nearly
> to
>
>

Cannot connect to an instance of SQL 2000

I have a server running SQL 2005 as the default instance, and sql server 2000 running as a named instance called SQL2000. When I try to connect to my SQL instance Servername\SQL2000, i am actually connected to my SQL 2005 instance. When I connect using SQL 2000's Entreprise manager, i get the error saying you need to use SQL 2005 connectivity tools. Any Ideas?

You can connect to SQL2K using 2K5 tools. Follow the directions at this link:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

|||

Sorry, but when i connect to the sql2000 instance using 2005 tools, i connect to the defuaalt SQL instance on this machine which is sql2005.

When I use Entreprise manager from the local system, i can connect to the sql 2000 instance just fine.

|||

Can you post the exact steps your doing including exactly what you're typing in and where?

Cheers,

Dan

Cannot connect to an instance of SQL 2000

I have a server running SQL 2005 as the default instance, and sql server 2000 running as a named instance called SQL2000. When I try to connect to my SQL instance Servername\SQL2000, i am actually connected to my SQL 2005 instance. When I connect using SQL 2000's Entreprise manager, i get the error saying you need to use SQL 2005 connectivity tools. Any Ideas?

You can connect to SQL2K using 2K5 tools. Follow the directions at this link:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

|||

Sorry, but when i connect to the sql2000 instance using 2005 tools, i connect to the defuaalt SQL instance on this machine which is sql2005.

When I use Entreprise manager from the local system, i can connect to the sql 2000 instance just fine.

|||

Can you post the exact steps your doing including exactly what you're typing in and where?

Cheers,

Dan