Troubleshooting
Problem
SQL Server documents several restrictions for OLE DB providers being used to access a remote table. These restrictions affect the use of the IBMDASQL OLE DB provider.
Resolving The Problem
SQL Server documents several restrictions for OLE DB providers being used to access a remote table. These restrictions affect the use of the IBMDASQL OLE DB provider.
Transact-SQL UPDATE and DELETE statements can reference remote tables only if certain conditions are met by the OLE DB provider that is used to access the remote table. The provider must support the following on the table being updated or deleted:
Transact-SQL UPDATE and DELETE statements can reference remote tables only if certain conditions are met by the OLE DB provider that is used to access the remote table. The provider must support the following on the table being updated or deleted:
- Bookmarks on the rowset opened through IOpenRowset.
- The IRowsetLocate and IRowsetChange interfaces on the rowset opened through IOpenRowset.
- The IRowsetChange interface must support update (SetData) and delete (DeleteRows) methods.
Because of these SQL Server restrictions, the IBM Access Client Solutions IBMDASQL provider might be limited to only DDL (Data Definition Language) and selecting data from Db2 on i with SQL Server.*
The error most commonly associated with the inability to perform DML (Data Manipulation Language) statements on tables linked by using the IBMDASQL provider is something like:
Msg 7352, Level 16, State 1, Line 7
The OLE DB provider "IBMDASQL" for linked server "RCH730A-DASQL" supplied inconsistent metadata. The object "(user-generated expression)" was missing the expected column "Bmk1000".
Which is a reference to the missing bookmark information.
Note: For additional information about restrictions, see the SQL Server documentation.The OLE DB provider "IBMDASQL" for linked server "RCH730A-DASQL" supplied inconsistent metadata. The object "(user-generated expression)" was missing the expected column "Bmk1000".
Which is a reference to the missing bookmark information.
* This information is anecdotal rather than being based on any in-depth research into the inner workings and details of SQL Server, remember this is IBM i support, not your first or best source for Microsoft SQL Server knowledge. But this did work for me in a number of tests, so I shared it.
To perform DML (inserts, updates, deletes) your table must be journaled.
Use 4 part naming in your statements where the table is identified by four parts: [LinkedServerName].[DatabaseName].[Schema].[Table].
The previous error about the missing "Bmk1000" column appears to happen only when the where clause in a delete or update statement is referencing non-numeric columns. When the where clause only has numeric values for numeric columns specified, it works without complaint. So if you have a way to uniquely identify the rows by using numeric columns, do so.
Q: What if I don't have any numeric columns that can be used to identify the affected rows?
A: There are two approaches you can use to resolve that.
Use 4 part naming in your statements where the table is identified by four parts: [LinkedServerName].[DatabaseName].[Schema].[Table].
The previous error about the missing "Bmk1000" column appears to happen only when the where clause in a delete or update statement is referencing non-numeric columns. When the where clause only has numeric values for numeric columns specified, it works without complaint. So if you have a way to uniquely identify the rows by using numeric columns, do so.
Q: What if I don't have any numeric columns that can be used to identify the affected rows?
A: There are two approaches you can use to resolve that.
- The first, perhaps easiest is to create a new linked server is to use the "Microsoft OLE DB Provider for ODBC Drivers" and specifying a System DSN created using the ACS ODBC driver for the data source value. In my testing, the Microsoft MSDASQL provider was able to provide the required bookmark values but that might be limited to a certain size result set.
- The second approach is to modify the table so that it has a unique numeric key. This can be done with a fairly simple SQL statement to add a generated numeric key. For example,
ALTER TABLE MIKSWENS.QCUSTCDT ADD COLUMN TBLID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO ORDER, NO CYCLE, NO MINVALUE, NO MAXVALUE, CACHE 20) NOT NULL NOT HIDDEN
Then, your DML statements can use that unique row ID to identify the rows to affect.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
457017342
Was this topic helpful?
Document Information
Modified date:
27 December 2019
UID
nas8N1014248