Publisher thinks it is a subscriber

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)

Publisher thinks it is a subscriber

Author

Topic

DavidD
Yak Posting Veteran

73 Posts

Posted - 2009-05-04 : 23:09:59

Hi All,

I am trying to add a column to a publication database and get the error:

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 361
The data definition language (DDL) command cannot be executed at the Subscriber. DDL commands can only be executed at the Publisher. In a republishing hierarchy, DDL commands can only be executed at the root Publisher, not at any of the republishing Subscribers.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 182
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

The database is definitly a publisher only and no subscriptions show up in replication monitor. Is there some way I can clean all subscriptions (existing or not) from the database?

Any other ideas?

Thanks
David

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-05-05 : 10:44:43

run the query


select * from master.sys.databases

and look for the bit values in the columns is_published , is_subscribed , is_distributor etc..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

DavidD
Yak Posting Veteran

73 Posts

Posted - 2009-05-08 : 01:56:35

Hi Dinakar,

Thanks for the reply. Sysdatabases had the issubscriber field as 0.

I worked it out though, the database was a copy of our live publication database which was restored from a backup onto the dev server. The backup file nust have included replication dags, because removing all subscriptions and then running sp_removedbreplication on the database before recreating the replication has sorted everything out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources