|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291,
Visits: 32
|
|
|
|
|
|
SSC 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
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Monday, March 18, 2013 5:10 PM
Points: 14,
Visits: 66
|
|
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
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:09 AM
Points: 1,558,
Visits: 1,396
|
|
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 Microsoft Certified Master: SQL Server 2008 Sr. Product Consultant and Chief SQL Server Evangelist @ Idera My book: Pro SQL Server 2008 Mirroring
|
|
|
|
|
Grasshopper
      
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
|
|
|
|
|
Forum 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.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Thursday, August 25, 2011 12:27 PM
Points: 2,
Visits: 39
|
|
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.
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260,
Visits: 1,977
|
|
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
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, February 04, 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/
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Wednesday, November 14, 2012 10:10 AM
Points: 39,
Visits: 55
|
|
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?
|
|
|
|