Microsoft KB Archive/239667

= BUG: Design Table in SEM Does Not Preserve NFR Property for IDENTITY =

Article ID: 239667

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q239667



BUG #: 53612 (SQLBUG_70)



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  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.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



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