Altering a column on a Replicated Table

  • Paul Ibison

    SSCertifiable

    Points: 5303

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • Igor Makedon

    Ten Centuries

    Points: 1235

    After adding a column by means of "(b) altering the table in-place" I am getting this message from Merge Agent:

    The schema script 'exec sp_repladdcolumn '[dbo].[child1]','AddColumnToReplicatedTable',[varchar(200)], '%', 1' could not be propagated to the subscriber.

    What might it be?

    Thanks.

    --Igor

  • Scott Cordon

    Valued Member

    Points: 64

    It is possible to add/remove columns to replicated tables for both transactional and merge replication as Paul says, however the 2 procedures provided cannot be used alone as they have some minor problems that need to be worked around.  The following I have used on SQL 2000, have not tried on 2005. 

    The two SPs that come with SQL are sp_repladdcolumn and sp_repldropcolumn.  sp_repldropcolumn works correctly, but you have to ensure you drop all dependent constraints from all subscribers and the publisher before using, otherwise it won't work.  sp_repladdcolumn works well for transactional if you specify a static default, e.g. not a function like newid or getdate, for merge replication it does not work by itself at all, changes made to the added column never get replicated.

    The limitation of functions is due to the sp_replxxxcolumn commands being replicated, so the functions fire on each server and each server gets unique values and each server is therefore different in these columns until the data is updated at some point in time.

    So, to overcome these few quirks, I use the following code (Look for the TODO sections to specify your needs):

    ---------------------------------

    /* For adding a column */

    declare @tblname sysname

    declare @colname sysname

    declare @strSQL nvarchar(1000)

    --TODO: Define the table you wish to add a column to and the new column name

    set @tblname='customers'

    set @colname='test'

    --TODO: Change the column definition to what you want

    exec sp_repladdcolumn @tblname, @colname, 'datetime not null default getdate()'

    --This checks if the table is being used in merge replication and if so, ensures that the new column will actually receive updates (there is a bug in SQL 2000 sp_repladdcolumn, this should be automatic)

    if (select count(*) from sysobjects where name='sysmergearticles' and type='U')>0

    begin

     if (select count(*) from sysmergearticles where name=@tblname)>0

      exec sp_MSaddmergetriggers @tblname

    end

    --This will ensure if you have a default that is a function, e.g. newid(), getdate() that the value becomes the same on all subscribers.  Without these lines, it will be different everywhere based on when the column was locally added.

    set @strSQL='update [' + @tblname + '] set [' + @colname + '] = [' + @colname + ']'

    exec (@strSQL)

    ---------------------------------

    ---------------------------------

    /* For removing a column

     TODO: If you have any constraints, run this on all subscribers first, apart from the last line.

           Then run it completely on the publisher */

    declare @tblname sysname

    declare @colname sysname

    declare @strSQL nvarchar(4000)

    --TODO: Define the table you wish to add a column to and the new column name

    set @tblname='customers'

    set @colname='test'

    --Remove all the constraints for this column

    -- find the constraints and build a query

    select @strSQL='alter table [' + @tblname + '] drop constraint [' + OBJ.name + ']' + char(13) + isnull(@strSQL,'') from sysconstraints as CON

    inner join syscolumns as COL

     on COL.id=CON.id and COL.colid=CON.colid

    inner join sysobjects as OBJ

     on OBJ.id=CON.constid

    where COL.name=@colname

    print @strSQL

    exec (@strSQL)

    --Only needed on the publisher, actually removes the column

    exec sp_repldropcolumn @tblname, @colname

    ---------------------------------

    Cheers,

    Scott

  • Robert Davis

    One Orange Chip

    Points: 28027

    Good article. I look forward to testing out the code in the article and above. I know a time will come when I will have to do this. My general procedure is that I will only add new articles or columns to merge replication and any column changes or drops wait until the next time I need to drop replication. Instead of doing a bunch of single changes, I do a lot of changes all at once.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Kim Nguyen-360420

    SSC Enthusiast

    Points: 152

    Hi Robert,

    My name is Kim, I am going thru the same issues that you had before, I was just wondering if yours worked out?  I am having problem with the insert statement.

    I have set up two servers, I was able to update and delete but I am not able to insert any rows either on Publiser or Subscriber.  I am using Merge repl. Sql server 2000, when I tried to insert a row in a table, and I got error msg 'Syntax error converting from a character string to uniqueidentifier', then I went to the table trying to delete the rowguid, but I keep getting the error msg "can't alter the table 'table_name' because it is being publised for replication".  So I went to the Publication folder to delete the publications in the 1st server (I have 2 different publications) I was able to delete the first one, but I am unable to delete the second one, I keep getting error msg 'error 3724: can't drop the store procedure 'procedure_name' because it is published for replication', so I tried to stop and start the 1st server, then tried to delete it, it didn't work either....I desperately am seeking for help, I am not sure what to do now so that I can run the insert statement???

    Please help!

    Thanks in advance for your help!

     

    Kim

     

     

  • Julie Daniels

    Valued Member

    Points: 65

    It is funny you write this article now. I have to do this exact same thing next weekend. The difference is for merge replication I am not doing to add a dummy column. I am created a backup table (select * into backuptable from original table). Then I am dropping the replicated column sp_dropreplcolumn, then added the column back with the expanded datatype varchar. I will then copy the data back from the backup table to the current table. update original table set currenttable.currcolname = backuptable.columnname. Same result a few less steps.

  • tjoshua

    Valued Member

    Points: 68

    Paul what you have will work but can cause some issues. I have compiled a list of the following scripts that we use all the time to drop tables out of your replicated dbs. Some of our replicated dbs are in the 600+ GB range across the wan so disabling replication 100% is out of the question.

    --First I stop the distribution and log reader

    --I then remove and rows corresponding to the article from distribution

    -- I have these in stored proc form but am going to send them in non-proc form

    delete from distribution..msrepl_transactions where xact_seqno in (select xact_seqno from distribution..msrepl_commands where article_id = (select artid from medical..sysarticles where name =' '))

    delete from distribution..msrepl_commands where article_id = (select artid from medical..sysarticles where name =' ')

    exec sp_dropsubscription @publication=' ', @subscriber='all'

    exec sp_droparticle @publication=' '

    exec sp_refreshsubscriptions @publication=' '

    --The article is now removed from replication and any work on it can be done

    To add the article back is where your scripts may have issues. Since we only use transactional replication it is the only place where I have seen the issue. The script

    exec sp_addarticle @publication=' ', @force_invalidate_snapshot = 0

    With transactional replication the above script creates the 3 procs for insert, update, and delete. For some reason MS has capped the length of those proc names if you do not manually specify them. We found this out due to having tables with similar names. IE. ah_table_name and ah_table_name_history. For some reason when we dropped the articles for modification and readded without specifying a name for the insert, update, and delete procs the sprocs for the two tables were given the same name.

    To fix this issue I wrote the following sproc for adding articles to replication. It is still in its infant stage and is crude but gets the job done. If you have any questions please email me at josh@joshuatipton.com.

    CREATE PROCEDURE dba_AddReplicationArticle

    @article_name sysname,

    @pub sysname = 'medical',

    @article_owner sysname = 'dbo'

    AS

    declare @upd_cmd nvarchar(255), @del_cmd nvarchar(255), @ins_cmd nvarchar(255)

    set @ins_cmd = 'CALL sp_MSins_'+@article_name

    set @del_cmd = 'CALL sp_MSdel_'+@article_name

    set @upd_cmd = 'MCALL sp_MSupd_'+@article_name

    exec sp_addarticle @publication = @pub, @article = @article_name, @source_owner = @article_owner, @source_object = @article_name, @destination_table =@article_name,

    @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',

    @ins_cmd = @ins_cmd, @del_cmd = @del_cmd, @upd_cmd = @upd_cmd, @filter = null, @sync_object = null,

    @auto_identity_range = N'false'

    if @@error <> 0

    begin

    print('Failure adding article')

    return

    end

    else

    begin

    exec sp_refreshsubscriptions @publication=@pub

    exec sp_reinitsubscription @publication=@pub, @article=@article_name, @for_schema_change= 1, @subscriber='all'

    end

    GO

    --You then restart the logreader and distribution agent and start the snapshot process. The table(s) will be sent across as soon as the snapshot agent is complete.

  • noeld

    SSC Guru

    Points: 96590

    My experience has been similar to Josh's

    addarticle in Transactional replication "creates" the storeprocedures for me on the subscribers. I have not found the name truncation issue that Josh mention though.

    Cheers,


    * Noel

  • Pedro R. Lopez

    SSC Eights!

    Points: 902

    Thank's Paul Ibison good article , thank god this have been greatly improve in 2005.

    some info http://msdn2.microsoft.com/en-us/library/ms143550.aspx

    My experience on merge replication ( if you have a database with to many DDL change before publication)

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=828315&SiteID=1

    Pedro R. Lopez
    http://madurosfritos.blogspot.com/[/url]

  • Jeff B.

    SSC Veteran

    Points: 253

    Unfortunately the technique of using SP_REPL_ADDCOLUMN and SP_REPL_DROPCOLUMN with the dummy columns would allow data loss if you didn't take the system down. If you don't take the system down, and data is changed in any way from the time the "dummy" column is created, to the time that the new correct column is added, that data would be lost.

    This could be avoided with a new snapshot, but there are many cases where this is not appropriate - such as Large Databases, slow networks, or purged systems.

    How do others work around this without causing data loss?

  • kinnon_2000

    Say Hey Kid

    Points: 702

    Fantastic article. many thanks.

    I have a question.

    I have a replicated table with a key field, lets call it 'Id'

    Id is an identity field not for replication, which is seeded with an increment of 1.

    My product is required to operate on multiple replicated databases, so if it cant reach one, it reaches another, and the data is shared accross all of them. I'm more of a developer than a sql server admin by the way so replication is someone elses cup of tea.

    I need the Id to be incremented as normal on each server, so the 'not for replication' Id field thing is a problem. I need it not to be independant of each location, and the Id's need to be shared.

    My replication management knowledge 0, and we have a guy who sorts that side of things, while i deal with data and tables. I perform updates using the sp_repl procedures, which has served me well so far.

    To solve this, I've written code in my application to determine the next id needed, and then insert to the table including the next id, rather than sql server determining the id on insert. problem is, i cant insert into that field, so need to strip off the identity seed stuff.

    I've tried the example, but of course it has issues dropping a key field.

    Can I make my TempId field the primary key, in order to drop Id, and then switch the key back?

    or is there a sp_repl stored proc that will allow me to alter the table structure to remove the increment from Id?

    This is a rarely used online tool, so the chances of two users selecting the same Id while connected to different databases are less than winning the national lottery. But maybe theres a better way?

    The guy who maintains our databases isn't around so cant talk to him about it at the moment.

    any advice?

    Kindest regards,

    kinnon

  • mEmENT0m0RI

    SSC Veteran

    Points: 254

    Hasn't this all been taken care of with DDL replication by setting the "Replicate Schema Changes” subscription property to "true" in 2005? Has been working for me up until it was "fixed" in SP3.

  • nadw95

    Valued Member

    Points: 59

    Thanks a lot.

    I have successfully changed a column on Replicated Table being used in merge replication.

  • david.blythner

    SSC Enthusiast

    Points: 143

    Great article, many thanks.

    A little background: I am BI developer on top of a sap installation (160 gb+) database.

    I do MS SQL 2005 for replication of the live database (Source), which gives me a mirror I can query the live out of.

    For a lot of tables, I do not have a flag saying when f.ex. customer basic data was updated last. This means that if I wish to do incremental updates to my datawarehouse, I need to use a tool that compares every column in every row with what is in the DW.

    This can get quite heavy on the system.

    THEREfore I would like to ask.

    Can I by manipulating the Mirror DB Customer Table with a new column f.ex DateOfLastDataChange and creating a trigger writing getdate() on insert/Updates, get a date for when the row was last manipulated?

    When I read the article it is about changing an existing column, not creating new ones. This is a problem for me, since I do not have access to our source system which is hosted out-house.

    Please advise, or give me more articles to read... by the way, love the site.

    best regards

    David

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply