I've been having trouble using SQL Server 2000 transactional replication to transfer Identity field values. In my environment, both the publisher and subscriber are SQL Server 2000 servers running Service Pack 3 (SP3), and the tables and articles on the subscriber have the exact same schema as those on the publisher (including Identity properties and foreign and primary key constraints and indexes). I understand that by default, transactional replication populates tables at the subscriber with no Identity property, but I really want to keep the property, so I pre-populated these table schemas with Identity property, foreign key, primary key, and indexes that are the same as on the publisher.Then, I toggled the Delete all data in the existing table button instead of dropping and re-creating it for each article. I've tried the transfer with and without the Not For Replication setting for Identity columns, but both attempts failed and returned the error Cannot update identity column 'LeadId'.(Source: EGRADSRV(Data source). What's the correct way to set up my transfer so that the subscription can keep Identity properties?
By default, the stored procedures that perform the update at the subscriber will set a column back to its original value, even if the column didn't change. Switching to an Identity column on the subscriber is what's generating the error.You can try one of two solutions. You can switch to using T-SQL UPDATE statements for updates of only this article. Or if you're running SQL Server 2000 SP1, you can create the update procedure by using the sp_scriptdynamicupdproc stored procedure. SQL Server 2000 Books Online (BOL) explains that "the default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed." If a column didn't change, the fact that the column is set back to itself typically doesn't cause a problem. But if the column is indexed, extra processing occurs. Sp_scriptdyanamicupdproc updates only the columns that have changed, which provides an optimal UPDATE string. BOL warns that "extra processing is incurred at runtime when the dynamic UPDATE statement is built. It is recommended that you test the dynamic and static approaches and choose the optimal solution."
—Vaquar Pirzada Platform Program Manager
Microsoft Project Team