Identity columns problem in replication among n no of subscribers

I have created Transaction Replication.
Nearly 100's of tables there in my database.
I have created Recordid (identity) column with primary key in each table and
all are incremented by 1.
Front End application in VB6 is 100% dependable on this column. Changes in
this column may spoil my work of VB6.
Synchronisation is done @. subscribers side using below activex component in
VB6
Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
Subscribers side does not show identity columns with primary key. It shows
only name of the column as INT.
Front application is failed to run @. subscribers side.
Pls suggest how to Replicate identity column among n no. of subscribers.
Best Regards
Sanjay
You will have to create different identity seeds and an increments of 2 on
both sides - on the subscriber a seed of 2 with an increment of 2, and on
the publisher an seed of 1 with an increment of 1.
Make sure the identity column has the not for replication property on it.
Then set your articles up to not modify the existing tables on the
subscriber and in your pre-snapshot script have your table creation scripts
(along with their respective indexes).
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>I have created Transaction Replication.
> Nearly 100's of tables there in my database.
> I have created Recordid (identity) column with primary key in each table
> and all are incremented by 1.
> Front End application in VB6 is 100% dependable on this column. Changes in
> this column may spoil my work of VB6.
> Synchronisation is done @. subscribers side using below activex component
> in VB6
> Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
> Subscribers side does not show identity columns with primary key. It shows
> only name of the column as INT.
> Front application is failed to run @. subscribers side.
> Pls suggest how to Replicate identity column among n no. of subscribers.
> Best Regards
> Sanjay
>
>
>
>
>
|||Dear Friend Hilary
Thanks for the help.
But one more solution can be there if I give different IDENTITY ranges to
each subscribers.
Pls help me If you know how to give diff IDENTITY ranges to each
subscriber.
Thanks in Advance
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
> You will have to create different identity seeds and an increments of 2 on
> both sides - on the subscriber a seed of 2 with an increment of 2, and on
> the publisher an seed of 1 with an increment of 1.
> Make sure the identity column has the not for replication property on it.
> Then set your articles up to not modify the existing tables on the
> subscriber and in your pre-snapshot script have your table creation
> scripts (along with their respective indexes).
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>
|||You certainly can, so you can go to your subscriber and do a
dbcc('mytablename', checkident, reseed, 10000000) and it will probably work.
However you will always have to monitor the range you assigned and adjust it
as necessary.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Friend Hilary
> Thanks for the help.
> But one more solution can be there if I give different IDENTITY ranges to
> each subscribers.
> Pls help me If you know how to give diff IDENTITY ranges to each
> subscriber.
> Thanks in Advance
> Best Regards
> Sanjay
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
>
|||Why do you need the identity property on the subscribers if you have
transactional replication, where the subscribers are treated as RO?
Perhaps this is being used as a failover server? If this is the case, the
easiest way to set it up is to enable automatic identity range management,
large range sizes and queued updating subscribers.
This way the subscriber can start entering data once the publisher is down
without any meddling with identity ranges on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear friend Hilary
Thanks for your full co-operation. My all doubts are clear now except below
2.
1. If I am having file called EMP with identity column Recid.
I want to give automatic range control on publisher for n no. subscribers
what will be the command ?
2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
able to store or pull others data range from 1 to 1000 or 2001 to 3000.
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
> You certainly can, so you can go to your subscriber and do a
> dbcc('mytablename', checkident, reseed, 10000000) and it will probably
> work. However you will always have to monitor the range you assigned and
> adjust it as necessary.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
>
|||For automatic identity range management on the publisher for queued and
merge replication you have to drop your publication and subscriptions and
enable this feature - in the articles tab of the create publication wizard.
A subscriber with a range of 1000-2000 will be able to receive replicated
commands for other ranges if the not for replication constraint is enabled.
Otherwise he will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%239Zlc%23yBHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear friend Hilary
> Thanks for your full co-operation. My all doubts are clear now except
> below 2.
> 1. If I am having file called EMP with identity column Recid.
> I want to give automatic range control on publisher for n no. subscribers
> what will be the command ?
> 2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
> able to store or pull others data range from 1 to 1000 or 2001 to 3000.
> Best Regards
> Sanjay
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
>