I have Adaptive Server Anywhere 8 as a Linked Server in MS Sql 2000.
"Select" and "Insert" statement would execute all right,
but "Update" statements would return an error.
Select * From OpenQuery(ASA_LinkedServer, 'Select * From Test_Table')
Insert Into OpenQuery(ASA_LinkedServer, 'Select * From Test_Table') Values (111, 'Test Insert')
would execute without problems.
Update OpenQuery(ASA_LinkedServer, 'Select * From Test_Table') Set Col2 = 'Test_Update'
however will return the following error
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'MSDASQL'.
The provider could not support a row lookup position.
The provider indicates that conflicts occurred with other properties or requirements.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned 0x80040e21:
Select * From Test_Table[PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]].
The Table is very simple(int and varchar),
The Linked Server is regestered in MS SQL through the OLE DB for ODBC and a ODBC Datasource to the ASA 8.
Does any body have an idea how to make this "Update statement work?
The article is directly related to my question.
If MS SQL is the Linked Serve - everything is OK.
With ASA 8 however I can't make it work.
I am using Remote Procedures to work around the problem.