OPENQUERY with Update to Oracle Issue
Currently we are running SQL Server 7 with SP1 installed on an NT box. I need to update a field in a table in Oracle. I setup a linkedserver in SQL Server using the Microsoft OLE DB Provider for ODBC (MSDASQL) and didn't have any problems selecting data from the linked Oracle tables using OPENQUERY. For example: SELECT * FROM OPENQUERY(STATSDEV, "Select * from CR_EXPORT") This query works fine.
However, now I need to update a field in the CR_EXPORT table. So I have written the following query and I tried to run it:
UPDATE OPENQUERY(STATSDEV,
"SELECT * FROM CR_EXPORT WHERE REQUEST_NUMBER = 1")
SET STATUS = 2
I got the following error message:
Server: Msg 7352, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
The Oracle table has a unique index, so I think that the issue might be associated with the version of the OLE DB provider or the Service Pack, but I can not find any documentation to support my theory. Any help you could give would be VERY appreciated!
OPENQUERY with Update to Oracle Issue (reply)
Jen,
Try downloading or installing the Oracle Provider for OLEDB and creating your link server using that provider. We received those same error messages using the other two Microsoft Providers OLEDB (for Oracle and For ODBC. Like you indicated the selects worked great but the updates and deletes are treated much differently. I just tested your UPDATE with the ORACLE Provider for OLEDB and it worked.
UPDATE OPENQUERY(STATSDEV,
"SELECT * FROM CR_EXPORT WHERE REQUEST_NUMBER = 1")
SET STATUS = 2
Let me know your results,
Jan
------------
Jen at 8/15/00 3:51:21 PM
Currently we are running SQL Server 7 with SP1 installed on an NT box. I need to update a field in a table in Oracle. I setup a linkedserver in SQL Server using the Microsoft OLE DB Provider for ODBC (MSDASQL) and didn't have any problems selecting data from the linked Oracle tables using OPENQUERY. For example: SELECT * FROM OPENQUERY(STATSDEV, "Select * from CR_EXPORT") This query works fine.
However, now I need to update a field in the CR_EXPORT table. So I have written the following query and I tried to run it:
UPDATE OPENQUERY(STATSDEV,
"SELECT * FROM CR_EXPORT WHERE REQUEST_NUMBER = 1")
SET STATUS = 2
I got the following error message:
Server: Msg 7352, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
The Oracle table has a unique index, so I think that the issue might be associated with the version of the OLE DB provider or the Service Pack, but I can not find any documentation to support my theory. Any help you could give would be VERY appreciated!
