FIX: Insert Through View May Cause Duplicated Identity Value (164691)

SYMPTOMS

Inserts performed through a view may generate duplicated IDENTITY values in the base table if SQL Server's 'open objects' is not configured high enough. If a table has a primary key or a unique index, the INSERT fails with either of the following errors:

2627 14 Violation of %s constraint '%.*s': Attempt to insert duplicate key in object '%.*s'.

-or-

2601 14 Attempt to insert duplicate key row in object '%.*s' with unique index '%.*s'.

The following message usually appears in SQL Server before the problem:

Warning: OPEN OBJECTS parameter may be too low attempt was made to free up descriptors in localdes() Run sp_configure to increase parameter value.

WORKAROUND

To work around this problem, use sp_configure to increase 'open objects' (see the documentation on DBCC MEMUSAGE to find the memory overhead of each open object).

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.