Problem replicating IDENTITY columns from Sybase ASE 15.5 to Sybase ASE 15.5

Question

I am trying to perform replication from Sybase ASE 15.5 to Sybase ASE 15.5 for a list of given tables via replication definitions and subscriptions defined in Replication Server(REP) Version 15.7.1. I found out that there is a table with one of the columns defined as IDENTITY. After creating the replication definition and subscription for that table, I am getting the below error messages in replication server:

Message from server: Message: 584, State 1, Severity 16 -- 'Explicit value specified for identity field in table 'customers' when 'SET IDENTITY_INSERT' is OFF'.

I have tried to run SET IDENTITY_INSERT customers ON on both of my primary and replicate database, but no luck.

Appreciate if you could help. Thanks!

Answer 1

Assuming your primary/replicate tables were create like such:

create table mytable
(id     int           identity
,name   varchar(100)
,dob    date
... snip ...

And assuming you want to replicate identity values from the primary table to the replicate table, you need to tell SRS that the id column is an identity column, and the way you do this is by designating the id column as an identity in the repdef, like such:

create replication definition mytable_repdef
with primary at ...
with all tables named mytable
(id     identity             <<<=== use 'identity' instead of the actual datatype
,name   varchar(100)
,dob    datetime
... snip ...

By designating the id column as an identity column in the repdef, SRS should now enclose all INSERTs (sent to the replicate database) in a set identity_insert on/off wrapper, and UPDATEs should be wrapped in a set identity_update on/off wrapper.