IBM Support

Error Msg 7320 when Performing an Update, Insert or Delete with Microsoft SQL Server Linked Server and IBM DB2 OLE DB provider.

Troubleshooting


Problem

When using Microsoft® SQL Server Linked Server with the IBM DB2 OLE DB (IBMDADB2) provider, the following error may occur: Error : "Msg 7320, Level 16, State 2, Line 1 Cannot execute the query against OLE DB provider "IBMDADB2.DB2COPY1" 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 updates on this table. The provider indicates that conflicts occurred with other properties or requirements."

Cause

When performing an UPDATE, INSERT or DELETE with Microsoft® SQL Server Linked Server, 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 which would allow SQL Server to request a different kind of cursor. Unfortunately instead SQL Server returns an error message indicating that the operation cannot be performed.

Resolving The Problem

This problem can be resolved by using the Microsoft® OLE DB provider for ODBC (MSDASQL) instead of the IBM OLE DB provider. MSDASQL uses the IBM DB2 ODBC/CLI driver, and with that driver, the cursor type can be manipulated to allow the operation to succeed.

In the db2cli.ini file on the client machine where SQL Server is running, add the following lines under the data source name (DSN) section of the db2cli.ini. For example, if the data source name is SAMPLE, add the following to the db2cli.ini file under the [SAMPLE] section as follows:


    [SAMPLE]
    DisableKeysetCursor=1
    PATCH2=6

DisableKeysetCursor=1 will tell the application that the IBM DB2 ODBC/CLI driver does not support Keyset cursors. PATCH2=6 will tell the application that scrollable cursors are not supported. These two keywords will allow SQL Server Linked Server to perform an INSERT/UPDATE/DELETE with the MSDASQL provider when using the IBM DB2 ODBC/CLI driver.

Once the db2cli.ini file is updated, SQL Server Linked Server should be restarted, and the link should be recreated to use the MSDASQL provider instead of the IBMDADB2 provider.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - Other","Platform":[{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1","Edition":"Enterprise Server;Express;Personal;Personal Developer's;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21441344