January 5, 2005 at 3:00 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist
Paul Ibison
Paul.Ibison@replicationanswers.com
August 3, 2005 at 7:22 am
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
January 19, 2006 at 6:19 am
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
January 20, 2006 at 10:32 am
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.
September 19, 2006 at 2:17 pm
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
November 30, 2007 at 5:20 am
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.
November 30, 2007 at 6:56 am
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.
November 30, 2007 at 7:42 am
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
November 30, 2007 at 8:38 am
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
November 30, 2007 at 2:12 pm
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?
March 20, 2009 at 6:42 am
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
April 13, 2009 at 12:26 pm
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.
May 13, 2010 at 4:18 am
Thanks a lot.
I have successfully changed a column on Replicated Table being used in merge replication.
July 15, 2010 at 2:47 pm
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply