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

• Microsoft SQL Server version 7.0

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.

STATUS

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

MORE INFORMATION

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

REFERENCES

Additional query words: SEM SSEM EM ent man entman repl table designer

Keywords : SSrvEntMan kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug