Adding Identity column to existing table.

I am trying to add a server managed ranged Identity column to an existing
table in my database and then have SQL Server provide the management of
subscribers ranges.
I have had success with this using a test database (a book walk-though
example) but now I want to add this to a real table in my production
environment (actually a copy of the database) and then test with several
Pocket PC Sql Server CE subscribers using merge replication.
I added a column named MyIdentity as a 'int' type and choose the 'Not for
Replication' option.
I have it set up with horizonal filtering using suser_sname() which is
correct giving each subscriber only the appropriate records.
The problem is that under 'Articles', 'Identity Ranges', the settings are
disabled as if they are not an option with the article I added the
MyIdentity column to.
Any ideas as to how to get this working. Your help is greatly appreciated.
Bill Mitchell
Bill,
when you create the publication there is a checkbox which gives the option
to 'automatically assign and maintain a unique identity range for each
subscription'. If this is not checked when you create the subscription then
it is assumed you will manually set the ranges and the option is then greyed
out. Please can you recreate the publication to check this is the case.
BTW setting it manually can also be useful (see
http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Paul,
Thanks, I think maybe I understand what was wrong earier. I tried your
suggestion but there was no tab on the article properties dialog as I was
creating the publication. Finally I added in a column as 'int' with the
Replicated set to "Yes". Then when I created the publication, the tab was
there and allowed me to choose for the server to manage the ranges.
If I understand correctly (I'm quite new to SQL Server), each table in a
merge replication solution in which the subscribers will be adding records
will need a single "int" column with the replication set to "Yes" prior to
creating the publication. Then during the creation of the publication, I
need to make sure and check for each of these tables (articles) ranges to be
managed. Is this correct and am I missing anything in this statement?
Thanks again,
Bill
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uKT3J1IREHA.2408@.tk2msftngp13.phx.gbl...
> Bill,
> when you create the publication there is a checkbox which gives the option
> to 'automatically assign and maintain a unique identity range for each
> subscription'. If this is not checked when you create the subscription
then
> it is assumed you will manually set the ranges and the option is then
greyed
> out. Please can you recreate the publication to check this is the case.
> BTW setting it manually can also be useful (see
> http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
>
|||Bill,
for merge you need the column specified as Yes Not For Replication on the
publisher. When creating the publication there is the checkbox to have SQL
Server manage identity ranges we talked about. On the subscriber there
should be the same Identity Yes Not For Replication attribute. This allows
the replication process to do identity inserts. The ranges avoid clashes. If
you are not going to have SQL Server manage the ranges then you can manually
do it yourself. This is required if you are doing a nosync subscription ie
the subscriber already has the data so the initialization process won't send
it down. There are details on Michael Hotek's site of nice algorithms to
avoid identity clashes (http://www.mssqlserver.com/replication/).
HTH,
Paul Ibison
|||Thanks for all of your help.
Bill
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eMO9NgMREHA.3420@.TK2MSFTNGP11.phx.gbl...
> Bill,
> for merge you need the column specified as Yes Not For Replication on the
> publisher. When creating the publication there is the checkbox to have SQL
> Server manage identity ranges we talked about. On the subscriber there
> should be the same Identity Yes Not For Replication attribute. This allows
> the replication process to do identity inserts. The ranges avoid clashes.
If
> you are not going to have SQL Server manage the ranges then you can
manually
> do it yourself. This is required if you are doing a nosync subscription ie
> the subscriber already has the data so the initialization process won't
send
> it down. There are details on Michael Hotek's site of nice algorithms to
> avoid identity clashes (http://www.mssqlserver.com/replication/).
> HTH,
> Paul Ibison
>
|||Never mind...I found it.
It's when I add an article I needed to go into this article's properties to set it before generating the snapshot.
Vince
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.