Odd SQL Server "Incorrect syntax" from HS connection
553292Mar 6 2009 — edited Mar 9 2009Environment: Oracle 11.1.0.6 on Windows XP. Both the client and server are on the computer, and I've defined an ODBC connection with "SQL Native Client" (SQLNCLI.DLL) driver. I set up the listener.ora and tnsnames.ora, created the database link. I'm trying to query a table with SQL*Plus. The table has a "login_user" column and no "fish" column:
-------
SQL> select "LOGIN_USER" from mfive.user_profile@m5dev_ss;
select "LOGIN_USER" from mfive.user_profile@m5dev_ss
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][SQL Native Client][SQL Server]Line 1: Incorrect syntax near
'LOGIN_USER'.[Microsoft][SQL Native Client][SQL Server]Statement(s) could not
be prepared.
ORA-02063: preceding 2 lines from M5DEV_SS
SQL> select fish from mfive."user_profile"@m5dev_ss;
select fish from mfive."user_profile"@m5dev_ss
*
ERROR at line 1:
ORA-00904: "FISH": invalid identifier
SQL> select * from mfive.user_profile@m5dev_ss;
select * from mfive.user_profile@m5dev_ss
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][SQL Native Client][SQL Server]Line 1: Incorrect syntax near
'LOGIN_USER'.[Microsoft][SQL Native Client][SQL Server]Statement(s) could not
be prepared.
ORA-02063: preceding 2 lines from M5DEV_SS
-------
So it knows what columns are supposed to be there, but if I actually use a column name or "*", it complains. I set up SQL Developer with the SQL Server driver and I can do the query fine. I don't know if it's in the Oracle, the ODBC, or the SQL Server. Any hints, please?