BUG: Design Table in SEM Does Not Preserve NFR Property for IDENTITY (239667)

SYMPTOMS

Using Design Table in SQL Server Enterprise Manager (SEM) to modify a table does not preserve the NOT FOR REPLICATION property.

For example, suppose you have a table with the primary key having the IDENTITY property, and the NOT FOR REPLICATION option is set. You publish this table for merge replication. If you open the table in the Design Table window of Enterprise Manager and make a change to it, the NOT FOR REPLICATION property on the IDENTITY column is lost. In this case, a side effect will be that replication can fail with the following error:

An explicit value for the identity column in table Table_Name can only be specified when a column list is used and IDENTITY_INSERT is ON.

WORKAROUND

To work around this problem, do not use Design Table in Enterprise Manager to make changes to the table. Instead, use Transact-SQL statements in Query Analyzer, OSQL, or ISQL. For example, use the ALTER TABLE statement instead.

MORE INFORMATION

To check whether the NOT FOR REPLICATION option is set, use the sp_help system stored procedure on the table.

REFERENCES

For more information on NOT FOR REPLICATION with IDENTITY, see the "Use the NOT FOR REPLICATION Option on the IDENTITY Property" topic in the SQL Server 7.0 Books Online.