Cannot delete publication

[dg043]

I have a problem with publications on a SQL Server 2000. I have a database called DTAD and this contains a publication of the same name. This is an old process and I no longer require it. However, when I try to delete it SQL Enterprise Manager freezes and remains in a 'Not Responding' state indefinately.
I have manually deleted ALL the merge jobs associated with this publication but I cannot delete the snapshot job. When I try to perform any operation on this job in Enterprise Manager, I receive the error

Error 14262: The specified @job_id ('xxxxx') does not exist

where xxxxx is a long hex number
I've even tried stopping the SQLServerAgent service and then deleting the publication but the same thing happens. Any ideas?


Dan Griffiths
Software Analyst
National Grid plc

 

[mrdenny]

Sounds like Enterprise Manager is caching the job list. Enterprise Manager is known for doing this.

Try closing Enterprise Manager and reopening it.

As for removing the replication, I assume that the replication is still there, just the jobs have been removed correct?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[dg043]

I have come across Enterprise Manager caching several times before, but in this case I have even resorted to rebooting the entire server and still the unwanted snapshot agent is in the list. I still can't delete the publication

Dan Griffiths
Software Analyst
National Grid plc

 

[mrdenny]

Here is sample code to remove the subscription

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- This batch is executed at the Publisher to remove 
-- a pull or push subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);

USE [AdventureWorks]
EXEC sp_dropsubscription 
  @publication = @publication, 
  @article = N'all',
  @subscriber = @subscriber;
GO

On the subscriber run the following.

exec sp_subscription_cleanup  @publisher = 'publisher'
        ,  @publisher_db = 'publisher_db'
     ,  @publication =  'publication'

After you remove all the subscriptions you can remove the publication. (Again sample code is given.)

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks'; 
SET @publication = N'AdvWorksProductTran'; 

-- Remove a transactional publication.
USE [AdventureWorks]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish', 
  @value = N'false';
GO

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[dg043]

Thanks, you're right but I have tried running those scripts so that I can now guarantee that no remote servers are subscribing to the publication. However, Enterprise Manager still locks up when you try to delete the publication.
This is really a moot point now anyway because I no longer have the problem. I noticed that the transaction log had become ridiculously oversized so I created a new database and DTS copied the contents of the DTAD database into it. The new database then didn't include the publication and when I deleted the original DTAD database, the corresponding snapshot agent dissappeared also

Dan Griffiths
Software Analyst
National Grid plc

 

[mrdenny]

That will work as well.

The last bit of code that I included would have been what deletes the publication so that you didn't have to do it through Enterprise Manager.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog