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.