Link to home
Start Free TrialLog in
Avatar of venk_r
venk_rFlag for United States of America

asked on

Replication failure while adding a column on the publisher

The replication failed with below error when trying to add a column to the table with datatype bigint and default value 0 which is already an article.The NFR property was set to Yes for the identity column.

"cannot insert explicit values for identity column in the table <tablename>"

Note:Its a transactional replication with Intialization from Backup

Avatar of sachitjain
sachitjain
Flag of India image

Ensure u have primary key index on source table and primary key column or columns is/are also selected to desired article.
ASKER CERTIFIED SOLUTION
Avatar of batchakamal
batchakamal
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
One option could be removing the article from the list of selected articles then reselect it with appropriate columns like including the one you mentioned. Might be possible that Identity column data in source and destination are not in sync due to some reason.
If you restored from backup your identity ssed values could be out of sync and trying to instert data with the same seed value on the subscriber ...  you could try and reseed to avoid the conflict

on the subscriber run ...

USE database;
GO
DBCC CHECKIDENT ("tablename", RESEED, 100,000);
GO
Avatar of venk_r

ASKER

thanks