Ora-00936: Missing Expression using Oracle OleDB Provider with OpenQuery

刘泽宇

现象：
Using the Oracle OleDb Provider with SQLServer may result in "ORA-00936: missing expression" when the following syntax is used with OpenQuery:

select * from openquery (SCOTTLINK,'SELECT [SYSDATE] FROM DUAL')

Error message:
OLE DB provider "OraOLEDB.Oracle" for linked server "SCOTTLINK" returned message "ORA-00936: missing expression".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT [SYSDATE] FROM DUAL" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "SCOTTLINK".


原因：
OpenQuery passes the query untouched to the provider, and needs to be passed in the native syntax for the database being used. Oracle does not support the [columname] syntax used by SQLServer.

处理方法：
Remove the brackets around the column name.

select * from openquery (SCOTTLINK,'SELECT SYSDATE FROM DUAL')