SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Altering a column on a Replicated Table


Altering a column on a Replicated Table

Author
Message
kinnon_2000
kinnon_2000
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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



mEmENT0m0RI
mEmENT0m0RI
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 160
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
nadw95
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 3
Thanks a lot.
I have successfully changed a column on Replicated Table being used in merge replication.
david.blythner
david.blythner
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 99
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search