Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to delete the unwanted publisher and subscriber


how to delete the unwanted publisher and subscriber

Author
Message
MAK-1128556
MAK-1128556
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 290
we are running SQL 2005,

I create a transactional replication.
when I restore the publisher db “A” into the subscriber server with different name “B” all the publisher that I have in publisher show up into the subscriber server but these publisher associated with “B” db, Also these publisher have no distributor.
Now I try to delete these but I am getting error “there is no distributor” attached to the publisher.
JeremyE
JeremyE
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3724 Visits: 4029
Have you tried running sp_removedbreplication on database "B" once it was restored to server "B"?

http://msdn.microsoft.com/en-us/library/ms188734.aspx
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4311
MAK-1128556 (8/17/2010)
we are running SQL 2005,

I create a transactional replication.
when I restore the publisher db “A” into the subscriber server with different name “B” all the publisher that I have in publisher show up into the subscriber server but these publisher associated with “B” db, Also these publisher have no distributor.
Now I try to delete these but I am getting error “there is no distributor” attached to the publisher.





Can you elaborate what are you trying to achieve by doing this ?

This will help us to answer more efficiently !

HTH,
Cheers

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
odanwada
odanwada
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1109 Visits: 438
Try using the following Syntax.

sp_removedbreplication [ [ @dbname = ] 'dbname' ]
[ , [ @type = ] type ]
MAK-1128556
MAK-1128556
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 290
Thanks for reply,
I try but getting error "Distributor server is not assign"
I try to delete unwanted publishers that copy into the subscriber when I restore the db into the subscriber but these publishere are associcated with subscribere db
mayank_tyagi
mayank_tyagi
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 261
Hello Mak ,

I think you had restored the database by this procedure :

1- Take instance offline
2- Move mdf ,ndf and ldf files to target server
3- Reattach these files to restore database

IF (I AM Right)
Begin

solution 1

1- configure Dummy replication to create distribution DB on Target server
2- then u can run sp_dbremovereplication or u can manually remove the Publication .
3- Disable the Dummy replication


End
crazy4sql
crazy4sql
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 4422
you will be getting this error as long your agents are running.
Go in activity monitor and kill those connection.
then right click on replication on SSMS and select disable publishing and distributor.... and follow the screen.

----------
Ashish
MAK-1128556
MAK-1128556
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 290
Thanks for reply,

1. I can't use Subscriber server as a distributor.
2. there is no agent job is running on Subscriber server because there is no distributor.
crazy4sql
crazy4sql
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 4422
what i can assume is, you restored the backup after replication configuration(if this is not the case then dont read further).
Can you try to restore some old backup of that database on suscriber and then give a try.

----------
Ashish
MAK-1128556
MAK-1128556
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 290
first I create a publisher then take a backup of the db and restore into the Subscriber and got unwanted publisher on my subscriber box.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search