2

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

  • 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? – Queue Mann Mar 12 '15 at 14:48
  • 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. – Scott Beeson Mar 12 '15 at 14:51
1

Try Open query

BEGIN TRANSACTION;

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

COMMIT
  • hi. did you ever figure out a way without openquery? since i have the same issue – Yisroel M. Olewski Oct 15 '16 at 19:16
  • Unfortunately for you, but fortunately for me, I no longer work with Caché so I did not pursue this further. Sorry. – Scott Beeson May 17 '17 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.