Redundancy requiring Identity columns on Subscriber - please help

Hi
I am just getting up to speed on replication so please forgive me if I
am missing something simple here......
Scenario
Application server (call it Test) that has SQL Server 2000 database
I am setting up replication for backup pupopses
(If Server A dies - point Application (Test) to Server B )
My Problem -
Many tables in the database have the identiy column set to YES
As the TEST operates, many rows uniquely added to the db using Identity
column as PKeys
I was using snapshot replication for test purposes....(no license for
Transactional)
On subscriber - created blank DB with YES (Not for Replication) option
set for the tables in question.
The Snapshot I created from Publisher, when I push to the subscriber -
it resets the Identify (YES - NFR) back to No
I redireecte dteh Aopplication server to Server B an dit fails to work
corrcectly.- cannot insert into row s where Identity column is set to
No
Identify needs to be set to YES (or YES not for Repl) in order for my
application server to work correctly.
Any ideas on how I could best achieve this?
Thanks in advance........
Kevha
right click on your publication, select properties, in the articles tab
select the browse button to the right of your tables. Click on the snapshot
tab. In the name conflicts section, select the keep delete existing data.
Now return to your subscriber, fix things the way you want them, and then
rereun your snapshot.
It should work this time.
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
<kevha@.hotmail.com> wrote in message
news:1132178886.227006.217160@.o13g2000cwo.googlegr oups.com...
> Hi
> I am just getting up to speed on replication so please forgive me if I
> am missing something simple here......
> Scenario
> Application server (call it Test) that has SQL Server 2000 database
> I am setting up replication for backup pupopses
> (If Server A dies - point Application (Test) to Server B )
> My Problem -
> Many tables in the database have the identiy column set to YES
> As the TEST operates, many rows uniquely added to the db using Identity
> column as PKeys
> I was using snapshot replication for test purposes....(no license for
> Transactional)
> On subscriber - created blank DB with YES (Not for Replication) option
> set for the tables in question.
> The Snapshot I created from Publisher, when I push to the subscriber -
> it resets the Identify (YES - NFR) back to No
> I redireecte dteh Aopplication server to Server B an dit fails to work
> corrcectly.- cannot insert into row s where Identity column is set to
> No
> Identify needs to be set to YES (or YES not for Repl) in order for my
> application server to work correctly.
> Any ideas on how I could best achieve this?
> Thanks in advance........
> Kevha
>
|||Hi Hilary
I still can't seem to get the problem solved.
Let me take it back a step -
Server A (source)
Server B (subscriber)
I want to replicate server A to server B
I set up Server A with Snapshot Replicationa sPublisher and
Distributor.
Database created on Server A - some tables with Columns set with
Identity = Yes
I set up Snapshot Replication
In the SnapShot Properties/ Articles I set 'Keep Existing Table
unchanged' option on.
I added a custom script in the Snapshot Properties tab that - Drops the
tables, recreates them with the IDENTITY NOT FOR REPLICATION option
included during table creation.
When I conduct the Synchronize - I get the following error -
Violation of Primary Key constraint 'x' insert duplicate key in object
'x'
My replication could happen once an hour (not real time critical so any
option that keeps the Identity filed intact appreciated)