Re: Identity columns
From: Hilary Cotter (hilaryk_at_att.net)
Date: 02/28/04
- Next message: Paul Ibison: "Re: Log Shipping Role Reversal"
- Previous message: Paul Ibison: "Re: Merge Replication Error"
- In reply to: Ralph: "Identity columns"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 28 Feb 2004 15:05:23 -0500
for immediate updating subscribers you don't need the NFR property as MSDTC
will take the insert/update/delete that is made on the subscriber, apply it
on the publisher, and then on the subscriber.
The NFR property is necessary when a replication process is doing inserts on
an identity column and qualifying the columns completely.
For instance consider this table
Create table idTest
(pk int not null identity(1,1) primary key,
charcol char(20)
)
an insert like this
insert into idTest (charcol) values ('test') will work
an insert like this
insert into idTest (pk,charcol) values (1,'test') will not work due to the
qualified identity value (1 in this case).
The problem is that replication processes like transactional replication or
queued updating take an insert like this
insert into idTest (charcol) values ('test')
and when replicating this statement turn it into this
insert into idTest (pk,charcol) values (1,'test')
which fails unless you have the NFR switch.
So if you are doing bi-directional transactional or queued updating this is
a problem. It also fails if you have an identity column existing on a table
you are replicating to on the subscriber.
HTH
"Ralph" <machio@karatekid.com> wrote in message
news:ioCdnY-KRfx276LdRVn-iQ@magma.ca...
> Hello all,
> I'm rather new with replication, and I have a question.
> I am setting up a database (sql 2000) for transactional replication. We
> want it for an immediate failover, in case of disaster on the main db. An
> up to the minute exact copy of the original database is what we need.
I've
> been experimenting with replication with a couple of machines in the
office
> and notice that you can't use Identity columns. Well.. you can if you use
> the "not for replication" flag.. however, we actually do need identity
> coloumns in our database.. the replication goes well.. but of course the
> target database is missing the identity column feature..
> Is there another way? is there something I am missing? or will I have to
go
> through the target database turning on Identity columns after a failure
> happens on the main database?
>
> Thanks! Hope this isn't too cryptic...
>
> ----------------------------------------------------------------
> Craig Farrell
>
> "If at first you don't succeed, then you probably shouldn't try skydiving"
>
>
>
- Next message: Paul Ibison: "Re: Log Shipping Role Reversal"
- Previous message: Paul Ibison: "Re: Merge Replication Error"
- In reply to: Ralph: "Identity columns"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
