SQL 2005 Merge Replication
in [Replication]
|
Prev: Sychronization error - MSSQL_REPL22037
Next: SQL 2005 subscriber and SQL 2000 publisher/distributor - Could not connect to distributor.
From: Venketash (Pat) Ramadass on 7 Jun 2006 11:42 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
From: Paul Ibison on 7 Jun 2006 12:33 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)
From: Venketash (Pat) Ramadass on 7 Jun 2006 17:17 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 "Paul Ibison" <Paul.Ibison(a)Pygmalion.Com> wrote in message news:%23unyRAliGHA.4144(a)TK2MSFTNGP02.phx.gbl... > 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) > >
From: Venketash (Pat) Ramadass on 7 Jun 2006 17:18 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 "Paul Ibison" <Paul.Ibison(a)Pygmalion.Com> wrote in message news:%23unyRAliGHA.4144(a)TK2MSFTNGP02.phx.gbl... > 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) > >
From: Paul Ibison on 8 Jun 2006 04:18
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) |