I'd have a look at dropping the trigger using sp_addscriptexec then readding
it after the column change.
You'll need to ensure the system is quiesced during this process.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hi there,

We are using SQL 2005 Merge replication between SQL 2005 Standard and SQL
2005 Express subscribers. We seem to not be getting database schema changes
right. Each time we try to make changes to the database for which a
publication has been created, there are errors for simple tasks such as...

ALTER TABLE [dbo].[Client] ADD
[VATNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

The error for that command was...

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 360
The DDL statement cannot be performed at the Subscriber or Republisher.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

There is a delete trigger of our own on that table as well as the merge
replication triggers. I have looked around for best practices for merge
replication and schema changes and found the following...

http://msdn2.microsoft.com/en-us/ms151870.aspx
http://www.quest-pipelines.com/newsletter-v6/1205_E.htm

....but they I can't seem to find an example of anyone else having this
problem. Maybe we are missing something obvious.

Any suggestions would be much appreciated...otherwise we will have to resort
to dropping the publication, making the changes and then re-snapshotting etc
etc which I can't believe is required.

Kind regards,

-Pat Ramadass

Hi Paul,

Thanks for the reply, no luck though I am afraid...exactly the same error.
As I said, it just seems strange that something as simple as an alter table
with a column add is doing this. It happens on other tables when I try to
alter them as well.

Any other ideas?

Thanks,

-Pat Ramadass

[quoted text, click to view]

Hi Paul,

Thanks for the reply, no luck though I am afraid...exactly the same error.
As I said, it just seems strange that something as simple as an alter table
with a column add is doing this. It happens on other tables when I try to
alter them as well.

Any other ideas?

Thanks,

-Pat Ramadass

[quoted text, click to view]

Do you get the same error using sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Sorry - meant to say sp_repladdcolumn in my previous post (no
air-conditioning here and it's like working in a fire-station!). I'm
thinking if it works, it would make sense if you had some SQL Server 2000
subscribers, so I'm wondering about the compatibility level is set
correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hello again,

It seems to run, but does not actually make any of the changes to the
publication database. Is this the correct behaviour for this command?

http://msdn2.microsoft.com/en-us/ms174360.aspx

Makes it sounds like it will be executed on the subscribers only.

This does not really solve the problem of why a simple alter table statement
does not work though?

Thanks.

-Pat Ramadass

[quoted text, click to view]

Hey again,

Okay, I think I know why this is happening, but I don't really know how else
to do it. I tried using repladdcolumn and got this error...

"Msg 21260, Level 16, State 1, Procedure sp_repladdcolumn, Line 148
Schema replication failed because database 'PSRLRGroupTest' on server
'TITANIUM' is not the original Publisher of table '[dbo].[Client]'."

Which then made me think about how this all started. We have a live
production database with a merge publication at subscribers who sync over
HTTPS. To test updates, I took the live db, made a backup, restored it as a
"Test" db and then created a new merge publication for our
developers/testers to use almost like a staging database so that we could
test updates and rollout only if successful. The previous errors such as...

"Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 360
The DDL statement cannot be performed at the Subscriber or Republisher.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted."

....were on that new database and publication. If you look at that and the
"not original publisher" error, it may be because it was a backup of the
production database. Forgive me if this was obvious to anyone.

Alter table seems to work fine on the production database, my question
therefore is this. How can one setup an identical testing environment as I
tried to. Can I not use a backup of the production db? Do I need to create a
completely new db, obviously based on the same schema and then take it from
there?

Thanks,

-Pat Ramadass

[quoted text, click to view]