May 5, 2008 at 5:08 am
I have a table that is replicated in two different publications. I need to rename the columns. I tried removing the tables from the replication and then altering the columns but for some reason the system thinks that the table is still being replicated. I found some documentation on this for Sql server 2000 but not 2005 and the fix for 2000 will not work in 2005. The error I am getting is Cannot alter column because table is part of replication but it isn't. It has something to with the system not dropping some information.
Has anyone else had this problem in SQL SErver 2005?
May 5, 2008 at 12:42 pm
I would try running the following (which you may have already done):
sp_dropmergearticle @publication= NAME_OF_Publication'
, @article= 'TABLE_NAME
, @ignore_distributor= 0
, @reserved= NULL
, @force_invalidate_snapshot= 1
, @force_reinit_subscription = 0
--- , @ignore_merge_metadata = ignore_merge_metadata (INTERNAL USE ONLY)
GO
I also found the following online (not sure if it is 2000/2005 specific):
Can you try this:
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
Hillary Cotter - (thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
I hope one of these helps....
Barbara
May 5, 2008 at 1:16 pm
I am doing snapshot replication and transaction replication but I have tried dropping with the SQL Syntax and that did not work. And the other fix is only for 2000. but thanks for trying.
November 5, 2010 at 7:40 am
Hi, I know it is some months since your original post and I truly hope that a resolution has been found. I thought that I would add my observations when I had the issue and the steps to resolve the error: Cannot alter column 'Column_Name' because it is 'REPLICATED'.
I faced this issue with Merge Replication on the single most import table. This table was replicated from a live server to one internally where we would perform auditing on changes.
The error was procedure by Altering a column (lets call it column A) in the table from int to BIGINT. First this was performed with a TSQL statement (listed below) instead of through the designer as in its infinite wisdom the designer would have dropped and recreated the source table. As soon as the change was made, replication monitor flagged an error reporting Cannot alter column 'Column_A' because it is 'REPLICATED'. This error was caused due to the sys.column setting on the subscriber having the 'is_non_sql_subscribed' value set to 1 for the Vehicle table. This means that the replication Meta Data has become inconsistent. Usually this value is set to 1 when running a snapshot and then updated to 0 once the snapshot has completed. In my case this had not been set back to 0 so it needed forcing.
Below are the steps I completed to resolve the issue:
1) Reset the value back to INT
2) Use Exec sp_enumeratependingschemachanges @publication = 'PubName'
Exec sp_markpendingschemachange @publication = 'PubName'
, @schemaversion = 'schemaversion_id'
, @status = 'skipped'
this ensured that schema change causing the replication to break was skipped to allow replication to work again.
3) Ensured I script all constraints, indexes and triggers on the subscriber table, then dropping them (ensure you're working on the subscribing db).
4) Removed the table from Merge Replication
5) Run a snapshot to ensure its up to date. - This stage clears the sys.column 'is_non_sql_subscribed' column back to 0.
5) Perform 'ALTER TABLE 'Tablename' ALTER COLUMN 'column A' BIGINT'
6) Add the table back in to merge replication
7) Run Snapshot process again to push changes to subscriber.
7) Re-apply and Constraints/Tiggers/indexes which have not been set on the table...in my case, my Auditing triggers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply