merge replication and identity field problems.

Hi there,
I have converted MS access database to sql 2000 database and front end
is in adp. i our db all of our table contain identity field as a
primary key and forgine key. I am using merge replication with the
publisher and distributer in the same server where original db is and
may have many subscriber (pull subscription) using msde who will
synchronize on demand. i change all the autonumber field in access as
identity field (not for replication) and relationship between table
(not for replication) is clear off. I am very much worried now if i
just publish the database and subscript is whether i am going to have
conflict with identity field which are primary key or its going to
workin fine. Actually i donot know how sql handel those identity field
with so many copy of subscriber. please give me some information how
should i proceed.
I have table call Job, jobcarrier, jobshots, joblogs, jobpersonnel,
etc where primary key is identity field and all the table contain
forgain key from job table. and our replicated database using the same
front end as we are using. please give me inf. how it work and what i
should do.
Thansk very much
Indra.
Indra,
you have a choice to either let SQL Server manage the identity ranges or do
it manually. If you select to synchronize your tables on initialization,
different seeds will be selected for each subscriber, and the size of the
allocated range is determined by yourself (on clicking the article
properties elipsis button a configuration form appears). This is probably
the easiest method. If you want to do it manually you might be interested in
Michael Hotek's algorithms to ensure no overlap
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Indra,
It would be easier for you to let SQL handle the identity values. Also I hope you are taking of specifying "Not for replication" for all your relationships.
Regards,
Karthik.
|||HI Paul,
I check all the information, BOL, artical, knowledgebase etc and try
to publishe merge replication with pull subscribtion and its not
working as the way it should work.
1. All the identity field has been assign as not for replication
2. all the relationship has been clear off the option (enforce
relationship for replication).
3. I could run the subcription and synchornzed the data.
4. i inserted in subscriber and in the publisher database it both give
the same identity field.
5. When i synchronized, it doesnot display any error message but the
data inserted at the subscriber has been deleted and data inserted at
the publisher has been trasfer to subscriber (the conflict with pk
data in subscriber has been deleted.)
6. when i check the pulication property the option for automatic
identity assign and maintain is not highlighted.
Could you please help me where am i making wrong and how i can do
this.
I will appreciate your help.
Thanks.
Indra.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<##LLZhNSEHA.3988@.tk2msftngp13.phx.gbl>...
> Indra,
> you have a choice to either let SQL Server manage the identity ranges or do
> it manually. If you select to synchronize your tables on initialization,
> different seeds will be selected for each subscriber, and the size of the
> allocated range is determined by yourself (on clicking the article
> properties elipsis button a configuration form appears). This is probably
> the easiest method. If you want to do it manually you might be interested in
> Michael Hotek's algorithms to ensure no overlap
> (http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
|||Indra,
automatic range management is not enabled by default. Using:
exec sp_MShelp_identity_property @.tablename = N'TestIdent', @.ownername =
N'dbo'
will let you know if it is enabled. To get SQL Server to manage the range
you'll need to put a check in the box on the article properties, Identity
range tab. As far as I know, this isn't possible to do retrospectively, so
you'll need to recreate the publication.
HTH,
Paul Ibison