Cannot delete publication or access properties

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Cannot delete publication or access properties

Author

Topic

kazuaki
Starting Member

6 Posts

Posted - 2004-01-23 : 12:56:21

I'm pretty new to replication, so please be gentle

I am replicating between two SQL Servers running 2000 sp3. I no longer need the push from server 2 to server 1. When I attempt to delete the publication from server 2, or access the properties, I get the following error:

SQL Server Enterprise Manager could not retrieve information about publication
'server 2'.

Error 2812: Could not find stored procedure ''.

I really need to get this issue resolved, but I'm stumped. I'm here to learn, so if you don't mind, educate me

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 13:06:04

When you created the publication, did you keep the option to allow stored procedures to be used? If so, then are they in the publication database. They are named like this: sp_MSdel_messages

This is an example from one of my publication databases. Messages is the table name. So do any sp_MS stored procedures exist? If they don't, then you would have had to select the option to not use stored procs or you need to not delete them.

Tara

kazuaki
Starting Member

6 Posts

Posted - 2004-01-23 : 13:15:45

I believe I left this option enabled. Is that the deafault setting, because I would likely have left it as is? I see that the master db has a bunch of sp_MS procs. So, what is my next step?

Thanks for the quick reply...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 13:17:43

Yes it is the default option. The stored procs wouldn't be in the master database. They would be in the publication database. For each published table, you would find three sp_MS stored procs:

sp_MSdel_<tablename>
sp_MSins_<tablename>
sp_MSupd_<tablename>

Do you have 3 for each published table?

Tara

kazuaki
Starting Member

6 Posts

Posted - 2004-01-23 : 13:24:08

The 3 procs do exist, but they are on the server that is on the recieving end of the data (subscriber). I do not see them on the publisher. Is that where they should be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 13:35:04

Not sure. I've always done a push subscription, never a pull one. If you are pulling them, then maybe they do belong on the subscriber. Which type did you use push or pull?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 13:36:24

You know I just reread your initial post and I don't think that it is complaining about the stored procedures that I am mentioning. I think it is complaining about system stored procedures. Let me dig up a script that deletes replication for you that way you can run it from Query Analyzer and get more informative error messages.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 13:39:14

Ok, here ya go:



use [GT]
GO

-- Dropping the transactional subscription
exec sp_dropsubscription @publication = N'GT_Reports', @article = N'all', @subscriber = N'SDDEVSQL3\GTW', @destination_db = N'GT_Reports'
GO

-- Dropping the transactional articles
exec sp_dropsubscription @publication = N'GT_Reports', @article = N'ALERTLOG', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'GT_Reports', @article = N'ALERTLOG', @force_invalidate_snapshot = 1
GO

-- Dropping the transactional publication
exec sp_droppublication @publication = N'GT_Reports'
GO

GT is the name of the publication database. GT_Reports is the name of the suubscribing database.
SDDEVSQL3\GTW is the name of the server where the subscribing database exists. ALERTLOG is the name of the published table.

So change these 4 values everywhere you find it in the script. Then run it.

Let me know what error that you get (the exact error message, it should say which line number as well).

Tara

kazuaki
Starting Member

6 Posts

Posted - 2004-01-23 : 14:08:03

When I first ran it, I got an error stating that it could not find the publication. I believe you made a mistake in your explanation, because youhad me putting the same values in for Publication and Destination_DB. I changed the values to what I thought they should be.

Here is my finished script:

use [MTEReports]
GO

-- Dropping the transactional subscription
exec sp_dropsubscription @publication = N'MTEReports', @article = N'all', @subscriber = N'AGOREMEDYSQL', @destination_db = N'ARSystem'
GO

-- Dropping the transactional articles
exec sp_dropsubscription @publication = N'MTEReports', @article = N'ReportDailyAgentPerformance', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'MTEReports', @article = N'ReportDailyAgentPerformance', @force_invalidate_snapshot = 1
GO

-- Dropping the transactional publication
exec sp_droppublication @publication = N'MTEReports'
GO

Here is the results:
Server: Msg 14071, Level 16, State 1, Procedure sp_changesubstatus, Line 308
Could not find the Distributor or the distribution database for the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor.
Server: Msg 14071, Level 16, State 1, Procedure sp_changesubstatus, Line 308
Could not find the Distributor or the distribution database for the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor.
Server: Msg 14046, Level 16, State 1, Procedure sp_droparticle, Line 161
Could not drop article. A subscription exists on it.
Server: Msg 14005, Level 16, State 1, Procedure sp_droppublication, Line 95
Could not drop publication. A subscription exists to it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 14:41:29

No, I made a mistake in my explanation. SQL Server generated the script for me using the replication generate script wizard. You have to run it on the publication database. But the script will point to the subscribing database. The USE statement needs to have the publication database. The @destination_db should point to the subscribing database. @publication points to the custom name that you selected during replication setup. In my case, it was the same name as the subscribing database.

Tara

kazuaki
Starting Member

6 Posts

Posted - 2004-01-23 : 14:52:40

OK, then I ran it correctly and received the errors listed in my previous post...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 14:56:23

Has the distribution database been deleted?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 15:09:18

http://support.microsoft.com/default.aspx?scid=kb;en-us;324401

Tara

kazuaki
Starting Member

6 Posts

Posted - 2004-01-23 : 15:28:14

quote:
Originally posted by tduggan

Has the distribution database been deleted?

I have not intentionally deleted the distribution database, but anything is possible. How can I verify what the distribution is called and that it exists? If I find it has been deleted, is there anything I can do at that point?

Sorry for all the novice questions. If this is getting too tedious for you and you want to drop it, I understand. I notice you are the only one who even wants to get involved in this...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 16:29:18

The distribution database is called distribution. If it has been deleted, then I'm not sure what can be done. But maybe if you deleted the rows in the publication database that start with MS. There should be 3. I would perform a FULL backup first before doing this though.

The distribution database being created is the first thing that is done when setting up replication. It doesn't have to be on the same server as the publication database or the subscription database.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources