How to query a server linked via ODBC

Question

I've looked at multiple other questions about querying linked servers, but nothing has worked for my setup.

Notes:

• I'm not using localdb at all. I just wanted to use SQLSMS for a remote cache database that is only accessible via the Intersystems ODBC driver.
• I edited out the actual table names, but they all have periods in them.
• MyDSN is not the actual DSN name.

Here is the structure and below the image are some of the things I've tried:

enter image description here

SELECT * FROM [MyDSN].[default].[Table.name]

Invalid object name 'MyDSN.default.Table.name'.

SELECT * FROM [localdb].[MyDSN].[default].[Table.name]

Could not find server 'localdb' in sys.servers.

SELECT * FROM [MyDSN].[default].dbo.[Table.name]

The OLE DB provider "MSDASQL" for linked server "MyDSN" does not contain the table ""default"."dbo"."Table.name"".

SELECT * FROM [MyDSN]..[default].[Table.Name]

The OLE DB provider "MSDASQL" for linked server "MyDSN" does not contain the table ""default"."Table.Name"".

SELECT * FROM [MyDSN].[default].[Table].[Name]

Invalid object name 'MyDSN.default.Table.name'.

Also, if I right-click on one of the tables and pick "Script table as SELECT" I get the following error:

enter image description here

• Queue Mann: Did you verify your ODBC DSN uses a valid login that can be authenticated by the remote server? In other words, is the login defined in the DSN listed in the Security Tab for the linked server?
• THE JOATMON: Yes, I meant to mention that. I mean, it's listing the tables properly. But also I right clicked on the link and verified it to be sure.

Answer 1

Try Open query

BEGIN TRANSACTION;
select top 10 * 
INTO #t
from openquery ("YOUR LINKED SERVER NAME",'SELECT * FROM [SOURCE TABLE NAME]);
COMMIT

• Yisroel M. Olewski: hi. did you ever figure out a way without openquery? since i have the same issue
• THE JOATMON: Unfortunately for you, but fortunately for me, I no longer work with Caché so I did not pursue this further. Sorry.