SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Error while creating Publication on sqlserver 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neellotus
Starting Member

India
14 Posts

Posted - 03/09/2013 :  05:47:14  Show Profile  Reply with Quote
Hi all,

i am re-creating new publication through New Publication Wizard but it showing Error :- "sp_MSreplagentjobexists can only be executed in the 'distribution' database.Object 'PUM_NM' does not exist or is not a valid object for this operation."

Re-creating because i deleted Replication in following manner:-
- Delete the subscriptions
- Delete the publications

after this i run following commands:-
alter database distribution set offline;
drop database distribution;

Then i realize alter & drop distribution db is very wrong so i re-attached distribution db on both server through SSMS then run following commands to remove replications:-

exec sp_removedbreplication @dbname='distribution'
exec sp_removedbreplication @dbname="STBData" (STBData :- from which articles to be publish).
Now i am creating again Publication on "STBData" Server but it is displaying above error.

i also tried Disable Publishing but it is showing error "Invalid object name 'dbo.syssubscriptons'

Pls help me how i can re-create publications,subscriptions & replication.
i will be very thankful to you.
Thanx

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/11/2013 :  00:32:39  Show Profile  Visit russell's Homepage  Reply with Quote
You can't attach distribution after dropping it.

What did you really do?

Restore from backup? Then what you did after ought to work.

Ok, how many publications do you have?
Dedicated distributor, or is it on the Publisher?

We'll help ya, but we need to know what really happened
Go to Top of Page

neellotus
Starting Member

India
14 Posts

Posted - 03/11/2013 :  01:02:10  Show Profile  Reply with Quote
Russell Sir,

Firstly lot of thanxxxxxxxxxxxxxx for your reply.

I have two (ERP & DATA)Server with SQL 2005 server installed on both server.I created ERP as Publication on STBData db and DATA as Subscription on STBData db.

Due to some reasons, firstly i deleted Subscription from DATA SQL server through right click on subscription under Replication>>Subscriptions menu of SSMS and by same procedure i deleted publications from ERP SQL server.Then i run following commands on ERP & DATA Server:-

alter database distribution set offline;
drop database distribution;

To re-create / implement fresh Replication on between both Server.After this i realize above commands are not good so i re-attached distribution db on both server through SSMS then run following commands on ERP Server to remove replications:-

exec sp_removedbreplication @dbname='distribution'
exec sp_removedbreplication @dbname="STBData" (STBData :- from which articles to be publish).
Now i am creating again Publication "STBData" on ERP Server but it is displaying error:- "sp_MSreplagentjobexists can only be executed in the 'distribution' database.Object 'PUM_NM' does not exist or is not a valid object for this operation."

i also tried Disable Publishing but it is showing error "Invalid object name 'dbo.syssubscriptons'




Go to Top of Page

neellotus
Starting Member

India
14 Posts

Posted - 03/11/2013 :  02:05:54  Show Profile  Reply with Quote
Dear Russell Sir,

Please suggest me some solution so that my replication work fine.

i will be very thankful to you.

Thanx
Neel
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/11/2013 :  20:52:30  Show Profile  Visit russell's Homepage  Reply with Quote
What is the result of this if you run it on the publisher?
SELECT db_id('distribution')


If it is NULL, then run (also at the publisher) sp_AddDistributiondb

If it is not NULL, thne run this at the publisher:
USE master
EXEC sp_removedbreplication 'STBData'


Edited by - russell on 03/11/2013 20:54:09
Go to Top of Page

neellotus
Starting Member

India
14 Posts

Posted - 03/12/2013 :  00:16:52  Show Profile  Reply with Quote
Russell Thanx for your reply.

i run :-

USE master
SELECT db_id('distribution')
Result :- 15,

Then i run :-
USE master
EXEC sp_removedbreplication 'STBData'

Result :- Command(s) completed successfully.

Then i start creating publication through New Publication Wizard but it showing error :-
"sp_MSreplagentjobexists can only be executed in the 'distribution' database.Object 'PUBLISHED' does not exist or is not a valid object for this operation."

Now what to do sir....
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/12/2013 :  12:31:40  Show Profile  Visit russell's Homepage  Reply with Quote
have a look at this thread
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000