sql server - Identical SQL query works on some tables but errors out on other tables same in the same DB

Question

I'm a finance person (little programming background) so I maybe asking something obvious for database programming experts but will appreciate any advice

Background:

I'm accessing Oracle NetSuite database via ODBC from Microsoft SQL Management Studio

Connection as a Linked Server is established successfully

I'm trying to execute the following SQL statements:

select * from [NETSUITE_SB2].[SB-B].[Administrator].[VARIANCE] -- success 

select * from [NETSUITE_SB2].[SB-B].[Administrator].[WTAX_JOB] -- "Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "NETSUITE_SB2" does not contain the table ""SB-B"."Administrator"."WTAX_JOB"". The table either does not exist or the current user does not have permissions on that table."

Upon some testing, it appears that whether the query is successfully run depends on whether the table name contains "_" (underscore) - for all tables without underscore I've tried, it worked, for all tables with underscore that I've tried, it failed.

Can anyone help me figure out how to overcome this?

Thanks in advance!

Answer 1

Instead of using a 4-part name in SQL Server and having SQL Server generate a query for the linked server, try using the OPENQUERY function and passing a query in the target system's SQL dialect directly. Something like:

select * 
from OPENQUERY([NETSUITE_SB2], 'select * from [SB-B].[Administrator].[WTAX_JOB]' )