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.
>

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.
>