Microsoft KB Archive/287093

= INFO: DB2OLEDB Supports UPDATE/DELETE from SQL Linked Server =

Article ID: 287093

Article Last Modified on 3/26/2004

-

APPLIES TO


 * Microsoft Host Integration Server 2000 Standard Edition
 * Microsoft SNA Server 4.0 Service Pack 4
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Service Pack 2
 * Microsoft SQL Server 7.0 Service Pack 3

-



This article was previously published under Q287093



SUMMARY
The Microsoft OLE DB Provider for DB2 (DB2OLEDB) that ships with Host Integration Server 2000 or SNA Server 4.0 Service Pack 4 supports SELECT, INSERT, UPDATE, and DELETE statements from SQL Server Linked Server.

All earlier versions of DB2OLEDB support only SELECT and INSERT statements when used with SQL Server Linked Server.



MORE INFORMATION
For an UPDATE or DELETE, the minimum requirements are as follows:
 * Host Integration Server 2000 or SNA Server 4.0 Service Pack 4
 * SQL Server 2000 or SQL Server 7.0 Service Pack 2

To execute INSERT, UPDATE, and DELETE statements using four-part linked server queries, the SQL Server Distributed Query Processor (DQP) feature uses bookmark support in the underlying OLE DB provider. The OLE DB Provider for DB2 does not support bookmarks. However, when DQP loads the provider using service components, the client cursor engine (CCE) is invoked. The CCE provides scrollable cursors and bookmarks.

INSERT, UPDATE, and DELETE statements when using four-part linked server queries can be supported using the CCE. However, some statements may fail or may update incorrect columns when there is no unique key column on the target tables, or when there are not enough unique values for the CCE to accurately determine which columns to update. In these cases, unpredictable results may occur, or DQP may fail to perform the update.

SELECT, INSERT, UPDATE and DELETE are also supported with passthrough query using OpenQuery syntax.

