Geek Answers Handbook

Changing column restriction null / not null = row replication errorguid

I have a database running under Sql server 2005 with merge replication. I want some of the FK columns to be “non-empty”, as they should always matter. The SQL server won't let me do this, this is what it says:

  • Unable to modify table. It is not valid to override the default restriction in the rowguid column that merge replication uses. Schema change failed during internal replication. For corrective action, see other error messages that accompany this error message. The transaction completed with a trigger. The package was interrupted.

I'm not trying to change the restrictions on a rowguid column at all, only on another column that acts like an FK. The other columns that I want to set are not equal to zero, since the record does not make any sense without this information (that is, the client, client name).

Questions: Is there a way to update columns as non-null without turning off replication and then turning it back on? This is even the best way to do this - should you use a constraint instead?

+7
sql-server sql-server-2005 constraints ssms merge-replication
source share
2 answers

SSMS seems to make changes to the tables, discarding them and re-creating them. So you just need to make changes using the T-SQL statement.

ALTER TABLE dbo.MyTable ALTER COLUMN MyColumn nvarchar(50) NOT NULL
+8
source share

You need a script to change your change in T-SQL statements, as SQL Server Management Studio will try to delete and recreate the table, not just add an extra column.

You will also need to add a new column to your posts.

Note that modifying a column in this way can be detrimental to Replication performance. Depending on the size of the table you are modifying, it can lead to replication of a lot of data. Please note that although the table can be modified in one statement, if 1 million rows are affected, then 1 million updates will be created on the subscriber, and not one update operator, as is commonly believed.

Hands, an improved approach to work ........

To complete this exercise you need to:

  • Back up your replication environment by completing the entire configuration.
  • Remove table from replication in as publishers / subscribers
  • Add a column to each Publisher / Subscriber.
  • Apply the update locally on each Publisher / Subscriber.
  • Add the table back to replication.
  • Confirm that the transaction is replicated.
+3
source share

More articles:


All Articles