Error messages when I perform an UPDATE, INSERT, or DELETE on a remote table by using the OpenQuery function. 7320, Level 16, State 2 Cannot execute the query against OLE DB provider "IBMOLEDB.IBMDBCL1" for linked server

Richel Bek 1 Reputation point
2021-03-23T20:22:46.57+00:00

Error messages when I perform an UPDATE, INSERT, or DELETE Transact-SQL statement on a remote table by using the OpenQuery function: 7320, Level 16, State 2 Cannot execute the query against OLE DB provider "IBMOLEDB.IBMDBCL1" for linked server. The provider could not support an interface required for the UPDATE/DELETE/INSERT statements. The provider indicates that conflicts occurred with other properties or requirements. The provider could not support insertion on this table. The provider indicates that conflicts occurred with other properties or requirements.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-03-23T22:26:59.403+00:00

    Yes, updates through linked servers can be a nightmare. The error messages are less than helpful. And there are so many components involved. SQL Server is just one of them. DB2 (in this case) is another. And then there is the OLE DB provider, which seems to the guilty party this time.

    There is all reason to consider a plan B instead of fighting this uphill battle. A plan B usually means having a client component to take care of the data movement.


  2. CarrinWu-MSFT 6,851 Reputation points
    2021-03-24T06:28:27.533+00:00

    Hi @Richel Bek ,

    The SQL Server OLE DB provider requires that a unique index exist on the underlying table for UPDATE or DELETE operations. If no unique index exists on a remote table, the following error occurs when an UPDATE or DELETE is attempted:

    Server: Msg 7320, Level 16, State 2, Line 1 Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required row lookup interface. 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.  
    

    This applies both to OpenQuery and four-part named UPDATE and DELETE operations. The problem is resolved by adding a unique index on the remote table.

    Please refer to Error messages when you perform an UPDATE, INSERT, or DELETE Transact-SQL statement on a remote table by using the OpenQuery function: "7357" and "7320" to get more information. Please let me know if it not work and provide more details about this issue.

    Best regards,
    Carrin


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.