question

RichelBek-3192 avatar image
0 Votes"
RichelBek-3192 asked CarrinWu-MSFT commented

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered RichelBek-3192 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

. I am using IBMOLEDB provider, As per IBM website the cause of the issue is SQL Server requests a server-side forward-only updatable cursor with the IBM OLE DB provider.
DB2 does not support this type of cursor, and reports this back to SQL Server, this problem can be resolved by using the Microsoft® OLE DB provider. Do you think there is any differences with Microsoft driver? Currently INSERT|UPDATE|DELETE works with EXCE command. However, it has performance overhead for large data.
You mentioned plan B having a client component for data movement, can you please provide more information about it.
Thanks,

https://www.ibm.com/support/pages/error-msg-7320-when-performing-update-insert-or-delete-microsoft-sql-server-linked-server-and-ibm-db2-ole-db-provider

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT commented

Hi @RichelBek-3192,

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.



· 17
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Currently INSERT|UPDATE|DELETE works with EXEC syntax with out adding unique index on the remote table. Do you think this the issue resolved if I install Microsoft OLDB Provider for DB2 ?

0 Votes 0 ·

You could always try that provider. It appears that it is relatively recently updated. (In difference some other providers that Microsoft are not maintaining anymore.) If nothing else, you may get a different error message.

I don't have any experience of DB2 myself, so I cannot say what is the best.

0 Votes 0 ·

You could always try that provider --> do you mean Microsoft OLDB Provider for DB2 driver ?

0 Votes 0 ·
Show more comments

Hi, because I am not work with DB2, so could you please provide more details about this error and then we can try our best to figure it out.

0 Votes 0 ·

Complete Error message:
Msg 7320, Level 16, State 2, Line 10
Cannot execute the query "SELECT column1,column2
FROM Db2server.schema.table" against OLE DB provider "IBMOLEDB.IBMDBCL1"
for linked server "DB2 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.

INSERT OPENQUERY (Linkedservername,'SELECT column1,column2 FROM db2server.schema.table') select name1,name2

0 Votes 0 ·
Show more comments

The SQL Server OLE DB provider

Carrin, what question are you answering? This is a problem relating to linked server running DB2.



0 Votes 0 ·

Thanks @ErlandSommarskog the problem is relating to a Microsoft SQL Server Linked Server to access DB2.

Issue: EXEC AT command addresses the limitations of OPENQUERY but it has performance impact for large data. The driver I am using is IBM OLE DB provider. I am not sure if Microsoft OLDB Provider for DB2 driver resolve the issue related with OPENQUERY.

0 Votes 0 ·

You can see here for some tips for operations linked server that are other SQL Server instances: http://www.sommarskog.se/dynamic_sql.html#remotedata

The idea is to send local data as XML to the remote server.

How much of that is applicable to DB2, I don't know. But maybe it can serve as inspiration.

1 Vote 1 ·
Show more comments