How did you init you subscriber? With a backup of publisher?
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:
> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:
> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>
Showing posts with label subscriber. Show all posts
Showing posts with label subscriber. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Identity values not replicated even though 'not for replication' i
How did you init you subscriber? With a backup of publisher?
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:
> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:
> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:
> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:
> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>
Monday, March 19, 2012
Identity Range reused by subscriber
We are running Windows 2000 server SP3a for the publisher/distributor and
MSDE with SP3a for the subscriber.
We have several tables that have identities for primary keys, and our
identity range handling has been working great, until now.
One of the tables decided to REUSE its existing range of 1000 (threshold is
set to 80%), so all the transactions are backing up at the subscriber since
the range
has already been used once before.
I tried to use the sp_adjustpublisheridentityrange, and it says it ran
successfully, but it is still doing the same thing.
Looking at the next available identity range on the table, it is showing
correctly for the next available block of 1000.
What could have happened, and how do I recover?
Thanks,
RS
It is hard to say what has caused this to happen. Obviously automatic identity range management has failed you for some reason.
I'd call up PSS on this problem.
Some people have had success my manually adjusting the constraint on the subscriber/publisher tables, and manually adjusting the values of MSrepl_identity_range in the distribution database.
And Mspub_identity_range.
Most DBA's will use the set it and forget approach to automatic identity range management. They will set their ranges once with what they feel are representative ranges for the lifecycle of the project. They don't have to worry about batch updates blowing
the range, or problems with automatic identity range management this way.
Of course if you are continually adding new susbcribers this might now work for you.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
MSDE with SP3a for the subscriber.
We have several tables that have identities for primary keys, and our
identity range handling has been working great, until now.
One of the tables decided to REUSE its existing range of 1000 (threshold is
set to 80%), so all the transactions are backing up at the subscriber since
the range
has already been used once before.
I tried to use the sp_adjustpublisheridentityrange, and it says it ran
successfully, but it is still doing the same thing.
Looking at the next available identity range on the table, it is showing
correctly for the next available block of 1000.
What could have happened, and how do I recover?
Thanks,
RS
It is hard to say what has caused this to happen. Obviously automatic identity range management has failed you for some reason.
I'd call up PSS on this problem.
Some people have had success my manually adjusting the constraint on the subscriber/publisher tables, and manually adjusting the values of MSrepl_identity_range in the distribution database.
And Mspub_identity_range.
Most DBA's will use the set it and forget approach to automatic identity range management. They will set their ranges once with what they feel are representative ranges for the lifecycle of the project. They don't have to worry about batch updates blowing
the range, or problems with automatic identity range management this way.
Of course if you are continually adding new susbcribers this might now work for you.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Identity Range not working for master
I have set up a publisher database and a subscriber database which is a replica of the publisher. For the identity fields I set it up so that they would have a range of 10 numbers with an 80% margin. I was testing this on the subscriber and replica at t
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:
> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:
> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]
identity range management
hi all, got a major problem, having done some mods to my replicated
database, the subscriber, no has primary key violations. im using sql server
identity range management so this should be fine,
i have performed an table upgrade in this way before.
scrpit out the repliation,
remove the replaction from the publisher by deleting the publication and
then using sp_removeddbrepliation.
makeing my changes
then running the script to rebuild the repliaction.
however this time, in serveral tables at the subscriber the id ranges seem
to have gone back to range that have been used.
is there anyway i can get the ranges updated bearing in mind that im using
the auto identity range management.
Thanks Andrew
Andrew,
you could use dbcc checkident to reseed manually, edit the check constraints
accordingly, and change the values in MSrepl_identity_range on the
subscriber. This table is used to check if the subscriber has used up its
range or reached the threshold. The new range you set would be obtained from
MSrepl_identity_range on the distributor, which is the master table and is
used to generate new values. The values in this table (MSrepl_identity_range
on the distributor) would need to be changed to avoid a future potential
conflict.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||After dropping the publication you will need to connect to the subscribers
and drop the replication check constraints on the tables. This is a
"problem" I have reported to Microsoft.
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:eMFNzaihFHA.1948@.TK2MSFTNGP12.phx.gbl...
> hi all, got a major problem, having done some mods to my replicated
> database, the subscriber, no has primary key violations. im using sql
server
> identity range management so this should be fine,
> i have performed an table upgrade in this way before.
> scrpit out the repliation,
> remove the replaction from the publisher by deleting the publication and
> then using sp_removeddbrepliation.
> makeing my changes
> then running the script to rebuild the repliaction.
> however this time, in serveral tables at the subscriber the id ranges seem
> to have gone back to range that have been used.
> is there anyway i can get the ranges updated bearing in mind that im using
> the auto identity range management.
> Thanks Andrew
>
|||hi all
i have stopped client connecteding to the subcriber
ok so if at the subscriber i change the next_seed value to the current_max
value in the msrepl_identity_range.
at the distributer i change the next_seed value to a range that is out of
the way.
If i then run the merge agent, will that pick up that the subcribers ranges
need changeing and change them to the value in the next_seed specified in
the distributor.
Thanks In Advance Andrew
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eij4KLjhFHA.576@.tk2msftngp13.phx.gbl...
> Andrew,
> you could use dbcc checkident to reseed manually, edit the check
> constraints accordingly, and change the values in MSrepl_identity_range on
> the subscriber. This table is used to check if the subscriber has used up
> its range or reached the threshold. The new range you set would be
> obtained from MSrepl_identity_range on the distributor, which is the
> master table and is used to generate new values. The values in this table
> (MSrepl_identity_range on the distributor) would need to be changed to
> avoid a future potential conflict.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Andrew,
I'm suggesting you bypass the automatic management and assign a range
yourself. Setting the values in msrepl_identity_range on the subscriber for
the range you want, msrepl_identity_range on the distributor to make sure it
is greater than the subscriber range, issuing a dbcc checkident, and
changing the check constraints will allow things to proceed as per normal,
and running the merge agent will register that anything has been changed
manually.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
database, the subscriber, no has primary key violations. im using sql server
identity range management so this should be fine,
i have performed an table upgrade in this way before.
scrpit out the repliation,
remove the replaction from the publisher by deleting the publication and
then using sp_removeddbrepliation.
makeing my changes
then running the script to rebuild the repliaction.
however this time, in serveral tables at the subscriber the id ranges seem
to have gone back to range that have been used.
is there anyway i can get the ranges updated bearing in mind that im using
the auto identity range management.
Thanks Andrew
Andrew,
you could use dbcc checkident to reseed manually, edit the check constraints
accordingly, and change the values in MSrepl_identity_range on the
subscriber. This table is used to check if the subscriber has used up its
range or reached the threshold. The new range you set would be obtained from
MSrepl_identity_range on the distributor, which is the master table and is
used to generate new values. The values in this table (MSrepl_identity_range
on the distributor) would need to be changed to avoid a future potential
conflict.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||After dropping the publication you will need to connect to the subscribers
and drop the replication check constraints on the tables. This is a
"problem" I have reported to Microsoft.
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:eMFNzaihFHA.1948@.TK2MSFTNGP12.phx.gbl...
> hi all, got a major problem, having done some mods to my replicated
> database, the subscriber, no has primary key violations. im using sql
server
> identity range management so this should be fine,
> i have performed an table upgrade in this way before.
> scrpit out the repliation,
> remove the replaction from the publisher by deleting the publication and
> then using sp_removeddbrepliation.
> makeing my changes
> then running the script to rebuild the repliaction.
> however this time, in serveral tables at the subscriber the id ranges seem
> to have gone back to range that have been used.
> is there anyway i can get the ranges updated bearing in mind that im using
> the auto identity range management.
> Thanks Andrew
>
|||hi all
i have stopped client connecteding to the subcriber
ok so if at the subscriber i change the next_seed value to the current_max
value in the msrepl_identity_range.
at the distributer i change the next_seed value to a range that is out of
the way.
If i then run the merge agent, will that pick up that the subcribers ranges
need changeing and change them to the value in the next_seed specified in
the distributor.
Thanks In Advance Andrew
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eij4KLjhFHA.576@.tk2msftngp13.phx.gbl...
> Andrew,
> you could use dbcc checkident to reseed manually, edit the check
> constraints accordingly, and change the values in MSrepl_identity_range on
> the subscriber. This table is used to check if the subscriber has used up
> its range or reached the threshold. The new range you set would be
> obtained from MSrepl_identity_range on the distributor, which is the
> master table and is used to generate new values. The values in this table
> (MSrepl_identity_range on the distributor) would need to be changed to
> avoid a future potential conflict.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Andrew,
I'm suggesting you bypass the automatic management and assign a range
yourself. Setting the values in msrepl_identity_range on the subscriber for
the range you want, msrepl_identity_range on the distributor to make sure it
is greater than the subscriber range, issuing a dbcc checkident, and
changing the check constraints will allow things to proceed as per normal,
and running the merge agent will register that anything has been changed
manually.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Monday, March 12, 2012
Identity problem. (Need generate on subscriber new identity values)
If you change the publication to queued updating, use
automatic identity range management and don't run the
queue reader, you should achieve what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
But where in that case subcribers updates will be stored? On distributor?
Is it known bug with incorrect handling identity values in replications? Or
it is my own issue?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:050b01c50853$c6e94010$a601280a@.phx.gbl...
> If you change the publication to queued updating, use
> automatic identity range management and don't run the
> queue reader, you should achieve what you require.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The subscriber updates will be held on the queue table at
the subscriber, or you could remove the subscriber
triggers. It is assumed that subscribers are read only in
normal circumstances, so any issues regarding data
changes at a subscriber and problems with identities on
the subscriber are not really catered for. Identities are
only coded for if the subscriber uses immediate updating
or queued updating.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
automatic identity range management and don't run the
queue reader, you should achieve what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
But where in that case subcribers updates will be stored? On distributor?
Is it known bug with incorrect handling identity values in replications? Or
it is my own issue?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:050b01c50853$c6e94010$a601280a@.phx.gbl...
> If you change the publication to queued updating, use
> automatic identity range management and don't run the
> queue reader, you should achieve what you require.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The subscriber updates will be held on the queue table at
the subscriber, or you could remove the subscriber
triggers. It is assumed that subscribers are read only in
normal circumstances, so any issues regarding data
changes at a subscriber and problems with identities on
the subscriber are not really catered for. Identities are
only coded for if the subscriber uses immediate updating
or queued updating.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Identity paradox
We have a problem with replicated tables.
We have on publisher and one subscriber(using Merge-replication). In our database there is one table with an identity column. In our publication we are using identity ranges in order to make sure that no identical records are created on both the server and the clients.
So far everything works fine. it is possible to create new records on the server and the clients without any conflicts.
But, when we tries to insert a record that is out of range on either the server or the clients we got an error; "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Client1', table 'W_Person', column 'ID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."
When not using replication it is possible to use SET IDENTITY_INSERT <table> ON. Is there any workaround without using the stored procedure Sp_adjustpublisheridentityrange?
//John & PeterThere is a check constraint created on the published table which prevents an value that doesn't meet the criteria of the check constraint from being entered into the identity column.
hth,
Shahin
We have on publisher and one subscriber(using Merge-replication). In our database there is one table with an identity column. In our publication we are using identity ranges in order to make sure that no identical records are created on both the server and the clients.
So far everything works fine. it is possible to create new records on the server and the clients without any conflicts.
But, when we tries to insert a record that is out of range on either the server or the clients we got an error; "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Client1', table 'W_Person', column 'ID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."
When not using replication it is possible to use SET IDENTITY_INSERT <table> ON. Is there any workaround without using the stored procedure Sp_adjustpublisheridentityrange?
//John & PeterThere is a check constraint created on the published table which prevents an value that doesn't meet the criteria of the check constraint from being entered into the identity column.
hth,
Shahin
Identity Management
Hi,
I have a merge replication going on. Say, I have a subscriber with an
identity range from 1000 to 1200 and I have only used 100 identities of that
range. For some what reason I have to reinitialise the subscription. What
publisher does is it allocates a new set of range to the table i.e. 1300 -
1400 to the article on subscription. As you can see I have simply wasted 100
identities i.e. from 1100 to 1200. Please note identity allocation is
happening automatically.
Is there a way that I can avoid the wastage?
How can I deduce using system stored procedure that what identities are in
use for each article on each subscriber?
Thanks
Vivek
This is normally not a concern.
Basically replication does not track which identity ranges are in place by
subscribers. All it does is track the last deployed identity range and the
range in use on the publication database.
While I don't recommend it, you can go to the subscriber and do a dbcc
checkident('TableName',reseed,101) and then run a sp_help TableName identify
the replication constraint which limits the identity values which can be
used in the subscriber, and modify it for the range you wish to use.
Again I don't recommend doing this.
You might want to check this link for more on this feature.
http://www.simple-talk.com/2005/07/05/replication/
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
"Vivek Sharma" <vivek_nz76@.hotmail.com> wrote in message
news:e97gXp$3FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a merge replication going on. Say, I have a subscriber with an
> identity range from 1000 to 1200 and I have only used 100 identities of
that
> range. For some what reason I have to reinitialise the subscription. What
> publisher does is it allocates a new set of range to the table i.e. 1300 -
> 1400 to the article on subscription. As you can see I have simply wasted
100
> identities i.e. from 1100 to 1200. Please note identity allocation is
> happening automatically.
> Is there a way that I can avoid the wastage?
> How can I deduce using system stored procedure that what identities are in
> use for each article on each subscriber?
> Thanks
> Vivek
>
I have a merge replication going on. Say, I have a subscriber with an
identity range from 1000 to 1200 and I have only used 100 identities of that
range. For some what reason I have to reinitialise the subscription. What
publisher does is it allocates a new set of range to the table i.e. 1300 -
1400 to the article on subscription. As you can see I have simply wasted 100
identities i.e. from 1100 to 1200. Please note identity allocation is
happening automatically.
Is there a way that I can avoid the wastage?
How can I deduce using system stored procedure that what identities are in
use for each article on each subscriber?
Thanks
Vivek
This is normally not a concern.
Basically replication does not track which identity ranges are in place by
subscribers. All it does is track the last deployed identity range and the
range in use on the publication database.
While I don't recommend it, you can go to the subscriber and do a dbcc
checkident('TableName',reseed,101) and then run a sp_help TableName identify
the replication constraint which limits the identity values which can be
used in the subscriber, and modify it for the range you wish to use.
Again I don't recommend doing this.
You might want to check this link for more on this feature.
http://www.simple-talk.com/2005/07/05/replication/
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
"Vivek Sharma" <vivek_nz76@.hotmail.com> wrote in message
news:e97gXp$3FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a merge replication going on. Say, I have a subscriber with an
> identity range from 1000 to 1200 and I have only used 100 identities of
that
> range. For some what reason I have to reinitialise the subscription. What
> publisher does is it allocates a new set of range to the table i.e. 1300 -
> 1400 to the article on subscription. As you can see I have simply wasted
100
> identities i.e. from 1100 to 1200. Please note identity allocation is
> happening automatically.
> Is there a way that I can avoid the wastage?
> How can I deduce using system stored procedure that what identities are in
> use for each article on each subscriber?
> Thanks
> Vivek
>
Subscribe to:
Posts (Atom)