Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Altering a column on a Replicated Table Expand / Collapse
Author
Message
Posted Wednesday, January 5, 2005 3:00 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist


Paul Ibison
Paul.Ibison@replicationanswers.com
Post #153870
Posted Wednesday, August 3, 2005 7:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, Visits: 79

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

Post #206847
Posted Thursday, January 19, 2006 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 6:46 PM
Points: 14, Visits: 68

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

Post #251982
Posted Friday, January 20, 2006 10:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537

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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #252457
Posted Tuesday, September 19, 2006 2:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 17, 2007 2:00 PM
Points: 10, Visits: 1

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

 

 

Post #309755
Posted Friday, November 30, 2007 5:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 17, 2008 1:26 PM
Points: 1, Visits: 7
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.
Post #428030
Posted Friday, November 30, 2007 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:52 AM
Points: 2, Visits: 87
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.



Post #428079
Posted Friday, November 30, 2007 7:42 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
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
Post #428112
Posted Friday, November 30, 2007 8:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 4, 2009 1:49 PM
Points: 160, Visits: 140
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/
Post #428144
Posted Friday, November 30, 2007 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:23 PM
Points: 39, Visits: 102
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?

Post #428328
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse