SQL Server Performance Forum – Threads Archive

Linked server – updates to DB2(OLE DB prov.) fails

We have an existing system, that was now migrated to a Consolidated SQL server environment. The old server was running on Windows 2000 with SQL2000 EE, SP3. New server has 5 instances of SQL2000 SE, SP3 running on Windows 2003. The specific system has a linked server setup to point to mainframe DB2, via OLEDB Provider, via CAE. I can’t seem to get the Linked Servers working again. It allows Reads and Writes, but not Updates. Gives following error: OLE DB provider ‘MSDASQL’ could not UPDATE table ‘[MSDASQL]’ because of column ‘ABC_CD’. The user did not have permission to write to the column. > [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.] OLE DB error trace [OLE/DB Provider ‘MSDASQL’ IRowsetChange:<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />etData returned 0x80040e21 It is definitely NOT the DB2, or SQL server privileges that is insufficient. I also eliminated the CAE versions (old box, test and prod, was on V6.1.0.31 and new on V7.1.0.40, but upgraded old box, and still works), and configurations. Also the SQL versions and SP-levels (there test box also on SE SP3). I created a new Linked Server and new Datasourse on one of there old boxes, and it works perfectly. If I do the exact same thing on the new server, it does not work. I compared auths, and even the entries in the sysservers and sysxlogins etc, and can’t see anything there either. I read the following articles: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm</a> /odbcproviderquery_based_updates.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefodbcprovspec.asp</a> According to all documentation on the OLE DB Provider for ODBC drivers (which we use in both the old and new environment), it indicates that cursor updates are not possible through OLE DB for ODBC. Here is the query that they are executing: UPDATE OPENROWSET ( ‘MSDASQL’, ‘DSN=DB2P;UID=abcdef<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />WD=abcdef’, ‘ SELECT A_CD, DESC, B_CD, C_CD FROM ABCTB.TEST_TABEL WHERE A_CD = ”12345” ‘ ) SET B_CD = ’12’ , C_CD = ‘012’ , DESC = ‘#TESTING 123’ There’s also indications that a registry key/switch exists to bypass a time-out issue(which they are also experiencing in the new environment). Article I read: Introduction to OLE DB OLE DB is a data access model from Microsoft. It uses the Component Object Model (COM) interfaces and, unlike ODBC, OLE DB does not assume that the data source uses a SQL query processor. Adaptive Server Anywhere includes an OLE DB provider named ASAProv. This provider is ailable for current Windows and Windows CE platforms. You can also access Adaptive Server Anywhere using the Microsoft OLE DB Provider for ODBC (MSDASQL), together with the Adaptive Server Anywhere ODBC driver. Using the Adaptive Server Anywhere OLE DB provider brings several benefits: Â· Some features, such as updating through a cursor, are not available using the OLE DB/ODBC bridge. Â· If you use the Adaptive Server Anywhere OLE DB provider, ODBC is not required in your deployment. Â· MSDASQL allows OLE DB clients to work with any ODBC driver but does not guarantee that you can use the full range of functionality of each ODBC driver. Using the Adaptive Server Anywhere provider, you can get full access to Adaptive Server Anywhere features from OLE DB programming environments. Can anyone PLEASE help with advise!?

]]>