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 Friday, March 20, 2009 6:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 25, 2009 5:04 AM
Points: 36, Visits: 59
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



Post #680335
Posted Monday, April 13, 2009 12:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 1, 2013 12:37 PM
Points: 8, Visits: 158
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.
Post #696039
Posted Thursday, May 13, 2010 4:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 10:42 PM
Points: 1, Visits: 3
Thanks a lot.
I have successfully changed a column on Replicated Table being used in merge replication.
Post #921122
Posted Thursday, July 15, 2010 2:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:44 AM
Points: 7, Visits: 96
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
Post #953492
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse