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_messagesThis 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 subscriptionexec sp_dropsubscription @publication = N'GT_Reports', @article = N'all', @subscriber = N'SDDEVSQL3\GTW', @destination_db = N'GT_Reports'GO-- Dropping the transactional articlesexec 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 = 1GO-- Dropping the transactional publicationexec 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 subscriptionexec sp_dropsubscription @publication = N'MTEReports', @article = N'all', @subscriber = N'AGOREMEDYSQL', @destination_db = N'ARSystem'GO-- Dropping the transactional articlesexec 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 = 1GO-- Dropping the transactional publicationexec sp_droppublication @publication = N'MTEReports'GOHere is the results:Server: Msg 14071, Level 16, State 1, Procedure sp_changesubstatus, Line 308Could 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 308Could 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 161Could not drop article. A subscription exists on it.Server: Msg 14005, Level 16, State 1, Procedure sp_droppublication, Line 95Could 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;324401Tara |
|
|
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 |
|
|
|