Oracle to SQL Server ODBC link - Invalid identifier when using WHERE clause

0
votes

I need to query data from SQL Server in Oracle using an ODBC database link. I have created the necessary configuration in Oracle to allow me to access the SQL Server database and query the data.

When I do:

select * from table@ODBC_LINK

i get all rows returned.

When I do:

select * from table@ODBC_LINK WHERE [Field] = 'Some Value'

I get an

ORA-000904 error; "Field" invalid identifier.

The field name is a valid field in the SQL-source database/table but it does not matter what I do, I am unable to use a WHERE clause as this always gives me the same ORA-error.

Using Oracle XE 11g and SQL Server 2012.

What is causing my problem?

1
sqlsql-serveroracleoracle11gdatabase-link
Oracle doesn't use brackets to escape names like SQL Server does. Omit them, or use double quotes if your names might contain special characters. - Jeroen Mostert
Uhm, Oracle XE 11g SQL = SQL Server 2012 ? Nope! - LukStorms
@der_roedie - tag the correct database here. - Sudipta Mondal
Tags are editted - der_roedie
@der_roedie . . . There was no need to edit the tags. The commenter obviously didn't read the question, because you are using two different database products. - Gordon Linoff

1 Answers

0
votes

Your query is being run in Oracle not SQL Server. The table comes from SQL Server, but not the parser.

So, first try no escape characters:

select * from table@ODBC_LINK WHERE Field = 'Some Value'

If you need to escape the name, use the Oracle escape character:

select * from table@ODBC_LINK WHERE "Field" = 'Some Value'