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 replicated. Show all posts
Showing posts with label replicated. 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 12, 2012
identity problem
I have multiple tables with identity column replicated (merge
replication) from Server A to Server B. During the initial setup, I
specified that sql agent manages the identity columns automatically and
runs in continuous mode. But a while, I got identity error and I had to
stop the agent, run sp_adjustpublisherientityrange and restart the
agent. After more research, I found out that this is a SQL bug and what
Microsoft recommend is exactly what I did, they also said the agent
should be scheduled to run every minute or so. However, in my case, this
is unacceptable because client may still run out of ID before the agent
restart and it causes the application error out. So here's what I did
1) build a insert trigger on every articles to check the range and call
sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
work because looks like sp_adjustpublisheridentity has no effect until
the identity column hits its limit
2) build a insert trigger on every articles to check and re-adjust the
check constraint. This somewhat working on the publisher side, however,
how to deal with in at the subscriber side? I know there's sp_help and I
can get the constraint from system tables, but they are more for human
eyes than for programs, just pain to parse and I just don't like putting
too much inside a trigger
any recommendations?
Thanks
Eric Li
SQL DBA
MCDBA
Eric
it is possible to create identity ranges which never overlap, in which case
you'll just need to give a large range to each subscriber and not need to
adjust it. Have a look at this article by Michael Hotek:
http://www.mssqlserver.com/replicati...h_identity.asp
Regards,
Paul Ibison
|||there are two approaches to this problem
1) follow the KB's advice
http://support.microsoft.com/default...&Product=sql2k
2) set your ranges to values where they never will be exceeded during the
lifetime of your replication solution.
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:ufUvuhoEEHA.580@.TK2MSFTNGP11.phx.gbl...
> I have multiple tables with identity column replicated (merge
> replication) from Server A to Server B. During the initial setup, I
> specified that sql agent manages the identity columns automatically and
> runs in continuous mode. But a while, I got identity error and I had to
> stop the agent, run sp_adjustpublisherientityrange and restart the
> agent. After more research, I found out that this is a SQL bug and what
> Microsoft recommend is exactly what I did, they also said the agent
> should be scheduled to run every minute or so. However, in my case, this
> is unacceptable because client may still run out of ID before the agent
> restart and it causes the application error out. So here's what I did
> 1) build a insert trigger on every articles to check the range and call
> sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
> work because looks like sp_adjustpublisheridentity has no effect until
> the identity column hits its limit
> 2) build a insert trigger on every articles to check and re-adjust the
> check constraint. This somewhat working on the publisher side, however,
> how to deal with in at the subscriber side? I know there's sp_help and I
> can get the constraint from system tables, but they are more for human
> eyes than for programs, just pain to parse and I just don't like putting
> too much inside a trigger
> any recommendations?
> Thanks
> --
> Eric Li
> SQL DBA
> MCDBA
replication) from Server A to Server B. During the initial setup, I
specified that sql agent manages the identity columns automatically and
runs in continuous mode. But a while, I got identity error and I had to
stop the agent, run sp_adjustpublisherientityrange and restart the
agent. After more research, I found out that this is a SQL bug and what
Microsoft recommend is exactly what I did, they also said the agent
should be scheduled to run every minute or so. However, in my case, this
is unacceptable because client may still run out of ID before the agent
restart and it causes the application error out. So here's what I did
1) build a insert trigger on every articles to check the range and call
sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
work because looks like sp_adjustpublisheridentity has no effect until
the identity column hits its limit
2) build a insert trigger on every articles to check and re-adjust the
check constraint. This somewhat working on the publisher side, however,
how to deal with in at the subscriber side? I know there's sp_help and I
can get the constraint from system tables, but they are more for human
eyes than for programs, just pain to parse and I just don't like putting
too much inside a trigger
any recommendations?
Thanks
Eric Li
SQL DBA
MCDBA
Eric
it is possible to create identity ranges which never overlap, in which case
you'll just need to give a large range to each subscriber and not need to
adjust it. Have a look at this article by Michael Hotek:
http://www.mssqlserver.com/replicati...h_identity.asp
Regards,
Paul Ibison
|||there are two approaches to this problem
1) follow the KB's advice
http://support.microsoft.com/default...&Product=sql2k
2) set your ranges to values where they never will be exceeded during the
lifetime of your replication solution.
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:ufUvuhoEEHA.580@.TK2MSFTNGP11.phx.gbl...
> I have multiple tables with identity column replicated (merge
> replication) from Server A to Server B. During the initial setup, I
> specified that sql agent manages the identity columns automatically and
> runs in continuous mode. But a while, I got identity error and I had to
> stop the agent, run sp_adjustpublisherientityrange and restart the
> agent. After more research, I found out that this is a SQL bug and what
> Microsoft recommend is exactly what I did, they also said the agent
> should be scheduled to run every minute or so. However, in my case, this
> is unacceptable because client may still run out of ID before the agent
> restart and it causes the application error out. So here's what I did
> 1) build a insert trigger on every articles to check the range and call
> sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
> work because looks like sp_adjustpublisheridentity has no effect until
> the identity column hits its limit
> 2) build a insert trigger on every articles to check and re-adjust the
> check constraint. This somewhat working on the publisher side, however,
> how to deal with in at the subscriber side? I know there's sp_help and I
> can get the constraint from system tables, but they are more for human
> eyes than for programs, just pain to parse and I just don't like putting
> too much inside a trigger
> any recommendations?
> Thanks
> --
> Eric Li
> SQL DBA
> MCDBA
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 Key
I know identity key in a table can cause problems when the table is replicated. Should we avoid using identity key altogether, if we don't know in advance whether replication will come into the picture?
Thanks for any advice.I'm not absolutely positive, but I would think that if the source and destination tables have the same identity seed (starting value) and identity increment, then there should be no data corruption.|||A problem may arise if the source table's identity key is in broken sequence.
Thanks.
Friday, March 9, 2012
Identity in replication
We are going to move Access 2002 database tables to SQL Server 2000. The
current Access tables are replicated. I plan to "unreplicate" them before
converting (otherwise all the repl system fields go over). However, many of
the tables have AutoNumber fields that we want to convert to Identity fields
in SQL. I read an article about Merge Replication (which is our plan) and
they said to set the Identity fields to NOT FOR REPLICATION. Is that true?
It seems the wording is confusing. Thanks.
David
David,
I've seen this setting for so long now that I'd forgotten how unintuitive it
seemed at first
. You're right for merge - the correct setting is Yes, Not
for Replication. This means it should function as a normal identity column,
apart from when rows originate from the replication synchronization process,
when effectively identity inserts are allowed. This setting isn't needed for
transactional or snapshot - just Identity - yes, because subscribers can't
change the data. If they are updatable, then the same applies as in merge.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
My concern is with laptop users that will be synching on occasion and
will be creating records in tables with Identity columns. Access
handles this with a random long integer. Does SQL Server do the same?
This is important because I prefer not to have to assign different
unique ID's for each laptop user. Access merely creates a random
negative or positive number. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||David,
SQL Server's identity column has a seed and increment - typically 1 and 1.
There's a description in BOL of its behaviour. With this in mind, after you
convert to SQL Server, use DBCC CHECKIDENT to reseed the identity value at
the publisher to be the highest value selected. After that you can use
automatic range management and have ranges allocated to each subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
current Access tables are replicated. I plan to "unreplicate" them before
converting (otherwise all the repl system fields go over). However, many of
the tables have AutoNumber fields that we want to convert to Identity fields
in SQL. I read an article about Merge Replication (which is our plan) and
they said to set the Identity fields to NOT FOR REPLICATION. Is that true?
It seems the wording is confusing. Thanks.
David
David,
I've seen this setting for so long now that I'd forgotten how unintuitive it
seemed at first
. You're right for merge - the correct setting is Yes, Notfor Replication. This means it should function as a normal identity column,
apart from when rows originate from the replication synchronization process,
when effectively identity inserts are allowed. This setting isn't needed for
transactional or snapshot - just Identity - yes, because subscribers can't
change the data. If they are updatable, then the same applies as in merge.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
My concern is with laptop users that will be synching on occasion and
will be creating records in tables with Identity columns. Access
handles this with a random long integer. Does SQL Server do the same?
This is important because I prefer not to have to assign different
unique ID's for each laptop user. Access merely creates a random
negative or positive number. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||David,
SQL Server's identity column has a seed and increment - typically 1 and 1.
There's a description in BOL of its behaviour. With this in mind, after you
convert to SQL Server, use DBCC CHECKIDENT to reseed the identity value at
the publisher to be the highest value selected. After that you can use
automatic range management and have ranges allocated to each subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Identity Error when replicating
I have some tables in a database that is tranactionally replicated to another
server.
Each table has a column which is the primary key as well as having the
Identity property set to Yes. The subscribing table has the same settings
for the corresponding column except that Indentity is set to 'Yes (Not for
replication)'.
When the tables replicate from an Insert transaction, everything is fine.
However, when doing an Update transaction, I get a "Cannot update identity
column 'RecordID'." error (Where RecordID is the column with the key and ID).
Is my server having mood swings?
How can I resolve this without setting the Indentity property to NO on the
subscriber?
p.s. I know that setting the subscriber column Indentity Property to Yes
isn't necessary since the publisher takes care keeping the key unique for me.
But if my publishing server ever goes down I would like a quick transition
to the subscribing server without having to change all the identity settings.
Roger,
on the subscriber there shouldn't be the identity attribute at all. You
could remove this attribute (identity - No), or edit the replication stored
procedures and comment out the second section. If you want it all to work on
failover you could use Queued Updating Subscribers, in which case the
replication stored procedures are coded differently and you won't have this
issue. Also, you'll have to consider the identity range, and the queued
updating option will do this for you if you select automatic range
management.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I just read the discussion between bert, Paul, and Hillary concerning this.
My scenario is not as intensive as bert's since I only have to deal with one
publisher and one subscriber.
I noticed bert was able to install SP4 beta but I couldn't find it. Is for
SQLServer2003? I am using SQLS2k.
How does one create Queued Update Subscribers? While I still be able to
retain my identity settings in my subscriber tables that use them?
"Roger Denison" wrote:
> I have some tables in a database that is tranactionally replicated to another
> server.
> Each table has a column which is the primary key as well as having the
> Identity property set to Yes. The subscribing table has the same settings
> for the corresponding column except that Indentity is set to 'Yes (Not for
> replication)'.
> When the tables replicate from an Insert transaction, everything is fine.
> However, when doing an Update transaction, I get a "Cannot update identity
> column 'RecordID'." error (Where RecordID is the column with the key and ID).
> Is my server having mood swings?
> How can I resolve this without setting the Indentity property to NO on the
> subscriber?
> p.s. I know that setting the subscriber column Indentity Property to Yes
> isn't necessary since the publisher takes care keeping the key unique for me.
> But if my publishing server ever goes down I would like a quick transition
> to the subscribing server without having to change all the identity settings.
|||Roger,
I'm not really sure what identity settings you currently have on your
subscriber. If you mean the actual Identity attribute, then queued updating
subscribers will add it (NFR). If you mean the identity value (number), then
this will be overwritten by the automatic range management unless you use a
nosync initialization. Nosync will have its ownb issues here in that you'll
have to manually reseed the subscriber tables on failover, so I'd not really
recommend it is you have a choice.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When you say "edit the replication stored procedures" are you talking about
the sp_MSins_TableName, sp_MSupd_TableName, etc, procs? If such is the case,
the section to comment out is after the 'else' statement?
If I go with Queued Updating, can I still keep the identity attribute
property of the column set to Yes on the subscriber?
Roger.
"Paul Ibison" wrote:
> Roger,
> on the subscriber there shouldn't be the identity attribute at all. You
> could remove this attribute (identity - No), or edit the replication stored
> procedures and comment out the second section. If you want it all to work on
> failover you could use Queued Updating Subscribers, in which case the
> replication stored procedures are coded differently and you won't have this
> issue. Also, you'll have to consider the identity range, and the queued
> updating option will do this for you if you select automatic range
> management.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Roger,
yes this is the section I was referring to, but I would definitely go with
queued updating subscribers instead. The Identity property should be Yes,
Not For Replication on the publisher and it'll be transferred in this way to
the subscriber. Also don't forget to enable automatic range management to
make your life easier, otherwise you'll have to reseed each identity column
after failover.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
server.
Each table has a column which is the primary key as well as having the
Identity property set to Yes. The subscribing table has the same settings
for the corresponding column except that Indentity is set to 'Yes (Not for
replication)'.
When the tables replicate from an Insert transaction, everything is fine.
However, when doing an Update transaction, I get a "Cannot update identity
column 'RecordID'." error (Where RecordID is the column with the key and ID).
Is my server having mood swings?
How can I resolve this without setting the Indentity property to NO on the
subscriber?
p.s. I know that setting the subscriber column Indentity Property to Yes
isn't necessary since the publisher takes care keeping the key unique for me.
But if my publishing server ever goes down I would like a quick transition
to the subscribing server without having to change all the identity settings.
Roger,
on the subscriber there shouldn't be the identity attribute at all. You
could remove this attribute (identity - No), or edit the replication stored
procedures and comment out the second section. If you want it all to work on
failover you could use Queued Updating Subscribers, in which case the
replication stored procedures are coded differently and you won't have this
issue. Also, you'll have to consider the identity range, and the queued
updating option will do this for you if you select automatic range
management.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I just read the discussion between bert, Paul, and Hillary concerning this.
My scenario is not as intensive as bert's since I only have to deal with one
publisher and one subscriber.
I noticed bert was able to install SP4 beta but I couldn't find it. Is for
SQLServer2003? I am using SQLS2k.
How does one create Queued Update Subscribers? While I still be able to
retain my identity settings in my subscriber tables that use them?
"Roger Denison" wrote:
> I have some tables in a database that is tranactionally replicated to another
> server.
> Each table has a column which is the primary key as well as having the
> Identity property set to Yes. The subscribing table has the same settings
> for the corresponding column except that Indentity is set to 'Yes (Not for
> replication)'.
> When the tables replicate from an Insert transaction, everything is fine.
> However, when doing an Update transaction, I get a "Cannot update identity
> column 'RecordID'." error (Where RecordID is the column with the key and ID).
> Is my server having mood swings?
> How can I resolve this without setting the Indentity property to NO on the
> subscriber?
> p.s. I know that setting the subscriber column Indentity Property to Yes
> isn't necessary since the publisher takes care keeping the key unique for me.
> But if my publishing server ever goes down I would like a quick transition
> to the subscribing server without having to change all the identity settings.
|||Roger,
I'm not really sure what identity settings you currently have on your
subscriber. If you mean the actual Identity attribute, then queued updating
subscribers will add it (NFR). If you mean the identity value (number), then
this will be overwritten by the automatic range management unless you use a
nosync initialization. Nosync will have its ownb issues here in that you'll
have to manually reseed the subscriber tables on failover, so I'd not really
recommend it is you have a choice.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When you say "edit the replication stored procedures" are you talking about
the sp_MSins_TableName, sp_MSupd_TableName, etc, procs? If such is the case,
the section to comment out is after the 'else' statement?
If I go with Queued Updating, can I still keep the identity attribute
property of the column set to Yes on the subscriber?
Roger.
"Paul Ibison" wrote:
> Roger,
> on the subscriber there shouldn't be the identity attribute at all. You
> could remove this attribute (identity - No), or edit the replication stored
> procedures and comment out the second section. If you want it all to work on
> failover you could use Queued Updating Subscribers, in which case the
> replication stored procedures are coded differently and you won't have this
> issue. Also, you'll have to consider the identity range, and the queued
> updating option will do this for you if you select automatic range
> management.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Roger,
yes this is the section I was referring to, but I would definitely go with
queued updating subscribers instead. The Identity property should be Yes,
Not For Replication on the publisher and it'll be transferred in this way to
the subscriber. Also don't forget to enable automatic range management to
make your life easier, otherwise you'll have to reseed each identity column
after failover.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Identity Columns on replicated data
CREATE TABLE SALES
(sale_id INT IDENTITY (1,1),
sale_region CHAR(2) )
When replicated, the IDENTITY property is lost. If the Publisher crashed and
I wanted to restore the replicated data off the subscriber, I could manually
edit the table and turn on the IDENTITY property under Enterprise Manager. Is
there another way? I can not find a way to do this with the ALTER TABLE
statement. TIA
The best way is to use a custom script in sp_addarticle to put the identity
property with the NFR clause on the subscriber.
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
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:95BE6E91-BDD0-4893-9FB8-47F6F92017B9@.microsoft.com...
> CREATE TABLE SALES
> (sale_id INT IDENTITY (1,1),
> sale_region CHAR(2) )
> When replicated, the IDENTITY property is lost. If the Publisher crashed
> and
> I wanted to restore the replicated data off the subscriber, I could
> manually
> edit the table and turn on the IDENTITY property under Enterprise Manager.
> Is
> there another way? I can not find a way to do this with the ALTER TABLE
> statement. TIA
(sale_id INT IDENTITY (1,1),
sale_region CHAR(2) )
When replicated, the IDENTITY property is lost. If the Publisher crashed and
I wanted to restore the replicated data off the subscriber, I could manually
edit the table and turn on the IDENTITY property under Enterprise Manager. Is
there another way? I can not find a way to do this with the ALTER TABLE
statement. TIA
The best way is to use a custom script in sp_addarticle to put the identity
property with the NFR clause on the subscriber.
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
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:95BE6E91-BDD0-4893-9FB8-47F6F92017B9@.microsoft.com...
> CREATE TABLE SALES
> (sale_id INT IDENTITY (1,1),
> sale_region CHAR(2) )
> When replicated, the IDENTITY property is lost. If the Publisher crashed
> and
> I wanted to restore the replicated data off the subscriber, I could
> manually
> edit the table and turn on the IDENTITY property under Enterprise Manager.
> Is
> there another way? I can not find a way to do this with the ALTER TABLE
> statement. TIA
Subscribe to:
Posts (Atom)