How to Alter a Table? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to Alter a Table?

Hi Gurus, How to Alter a table which is published for replication.
How to fix this below error.VERY VERY Urgent Please. Msg 4929, Level 16, State 1, Server HYDMSSQL1, Line 1
Cannot alter the table ‘exch_tools_trade’ because it is being published for
replication. …Upgrading the trade_item_fill table …
Msg 4929, Level 16, State 1, Server HYDMSSQL1, Line 1
Cannot alter the table ‘trade_item_fill’ because it is being published for
replication. Thanks & Regards raj
It’s in books online under "Schema Changes on Publication Databases" Tradeco wholesalers, drop shippers & trade network
If you want to add a column, you can execute following statement at Publisher. sp_repladdcolumn N’Customers’,N’Cat1′,N’varchar(25)’,N’data’,0,NULL,1,1 This will add a new column called Cat1 of varchar(25) to Customers table in Data publisher. sp_repldropcolumn @source_object = ‘Customers’ , @column = ‘Cat1’ , @schema_change_script = NULL , @force_invalidate_snapshot = 1 , @force_reinit_subscription = 1 Above statement will drop a column called Cat1 from Customers table in Data publisher. However, problem comes when you want to modify a column as there is no direct stored procedure for that. Nevertheless, by combining above two stored procedures, you can achieve that. CREATE TABLE #TEMP (ID VARCHAR(15), DES VARCHAR(100)) go INSERT INTO #TEMP Select CustomerID,Fax from Customers go sp_repldropcolumn @source_object = ‘Customers’ , @column = ‘Fax’ , @schema_change_script = NULL , @force_invalidate_snapshot = 1 , @force_reinit_subscription = 1 go sp_repladdcolumn N’Customers’,N’Fax’,N’varchar(25)’,N’data’,0,NULL,1,1 go Update Customers Set Fax = (select DES from #TEMP where Id = Customers.CustomerID) drop TABLE #TEMP Above query will change that FAX column to varchar(25) and previous data will be inserted later. In all the three cases, you only have to run this at publisher and the changes including the schema changes will be replicated to the subscribers. In SQL Server 2005, there is a functionality called DDL replication where DDL changes also replicated. —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

]]>