Cannot remove push subscription on SQL Server 2000

Hi,

We have created replication from one production server to another backup server by using SQL scripts. These scripts have been generated by SQL Server after manual initial setup. When I ran these scripts they did not add the subscription for the backup server because a stored procedure were missing named something like sp_addsubscription. Therefore I ran the script on the production server and created the subscription manually on the backup server. But I made an error, I created a push subscription first but I should have created a pull subscription. Now I can't delete the push subscription. I don't even get a error message. I can't press delete and when right clicking on it I can only choose Help. The subscription has the following properties:

Type: push
Status: retrying
Last action: The process could not connect to distributor 'PSERV'
Replication type: Snapshot

Anyone know how I can delete this push subscription on my backup server?

Best,
/M

When you right-click on the subscription, are you connected to the production/publisher or backup/subscriber.

Try to do this on the publisher and see if you can delete it.

You can also call sp_dropsubscription (with the appropriate parameters) on the production server to drop the subscription.

|||

Nothing at all exist on the production server. The distribution and the publication has been removed. Only the push subscription exist at the backup server. And I can't delete that.

How can I use sp_dropsubscription. Can I use that from Query Analyzer. Does anyone have a SQL example for that?

Best
/M

|||

If you just want to get rid of replication on the backup server use sp_removedbreplication.

Note that this will remove all types of replication: merge and Transactional.

Use this only if you wish to remove all of replication.

|||

Additionally you can use the following:

sp_subscription_cleanup: for Transactional -- Removes metadata when a subscription is dropped at a Subscriber. For a synchronizing transaction subscription, it also includes immediate-updating triggers. This stored procedure is executed at the Subscriber on the subscription database.

sp_mergesubscription_cleanup: for Merge -- Removes metadata, such as triggers and entries, in sysmergesubscriptions and sysmergearticles after the specified merge push subscription is removed at the Publisher. This stored procedure is run at the Subscriber on the subscription database

|||

I used sp_removedbreplication 'db_name' to remove everything at the backup server. It worked as I wanted...

Thanks very much!

Now, I am happy again... for a short time :)

Best
/M