Error while creating Publication on sqlserver 2005

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 2005 Forums

Replication (2005)

Error while creating Publication on sqlserver 2005

Author

Topic

neellotus
Starting Member

15 Posts

Posted - 2013-03-09 : 05:47:14

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

5072 Posts

Posted - 2013-03-11 : 00:32:39

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

neellotus
Starting Member

15 Posts

Posted - 2013-03-11 : 01:02:10

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'

neellotus
Starting Member

15 Posts

Posted - 2013-03-11 : 02:05:54

Dear Russell Sir,

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

i will be very thankful to you.

Thanx
Neel

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-11 : 20:52:30

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'

neellotus
Starting Member

15 Posts

Posted - 2013-03-12 : 00:16:52

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....

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-12 : 12:31:40

have a look at this thread

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources