SQL Server Performance

How to Alter a Table?

Discussion in 'Performance Tuning for SQL Server Replication' started by krajdba, Jun 11, 2007.

  1. krajdba New Member

    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
  2. twoboats New Member

  3. dineshasanka Moderator

    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/

Share This Page