PRB: Error 7306 When Updating or Deleting Data Using a Linked Server in SQL Server 2000 (814581)

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SYMPTOMS

When you use variables in the UPDATE or DELETE Transact-SQL statement to update or delete data in remote tables by using a linked server and the Other data source Provider name property is Microsoft OLE DB provider for SQL Server, you may receive the following error message:

Server: Msg 7306, Level 16, State 2, Line 4
Could not open table '"database name"."dbo"."table name"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

CAUSE

You may receive this error message if the UNIQUE index or PRIMARY KEY constraint does not exist on the remote table for Microsoft OLE DB providers to run an UPDATE or a DELETE Transact-SQL statement. You may also receive this error message if you have not selected the Allow InProcess option for the linked server. To select this option, in the Linked Server Properties dialog box, click Provider Options, and then click to select Allow InProcess.

WORKAROUND

To work around this problem, use one of the following methods:

Create a UNIQUE or PRIMARY KEY constraint on the remote table.

-Or-
WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
Delete the registry key DynamicParameters.The registry key path is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB.

MORE INFORMATION

For more information, visit the following Microsoft Web sites:

Rules for Updating Results
Accessing External Data
Configuring Linked Servers
Configuring OLE DB Providers for Distributed Queries
Creating an Index
UPDATE and DELETE Requirements for OLE DB Providers

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

218995 BUG: Error "Invalid Object Name" on Linked Server Table Through OLEDB