How to query a server linked via ODBC

linked-serverodbcssms

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

Best Answer

Try Open query

BEGIN TRANSACTION;

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

COMMIT

Related Solutions

Sql-server – Extracting data from SQL Server linked server

Check the link server setup and see HOW the users connect to the link server. This can be done by going to Server Objects in SSMS -> Linked Servers -> [Link server name] -> right click then select Properties -> security tab. It can be done without a security context, using the current login's security context, or it can be done using a specific login. Find out which account is being used and then make sure that account has access to the table on the linked server.

Sql-server – SQL Server –> Informix DB Linked Server

We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL Server instance for warehousing purposes, and was told by our resident SME that this was impossible; only a single Informix instance could be referenced per server.

I would consistently receive the same errors as you, and nothing I was able to dredge up online proved helpful.

Ultimately, the solution had several parts, of which you've done the first few:

The correct IBM Informix ODBC Driver must be installed on the database server.
Use the SetNet32 application provided with the drivers and set up the Server Information tab appropriately. Additionally, set up a corresponding System DSN entry.
The trick with the Linked Server setup is to use the following (items in codeblocks are literal):
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Product Name: Ifxoledbc
- Data Source: $YourSystemDSNName
- Provider String
  - Driver = {IBM INFORMIX ODBC DRIVER}
  - Database = $YourDatabaseName
  - etc...

If you're more of a manual SQL kind of person, see below. The provider string is pulled into a variable for inline commenting purposes only, while still having executable code. Modify as needed:

DECLARE @provider NVARCHAR(4000);
SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};'
  + N'DATABASE= ;'  --Informix DB name
  + N'HOST= ;'      --Informix Hostname or IP adddress
  + N'SERVICE= ;'   --Informix service name, ex.  ifx1_tcp1
  + N'PROTOCOL= ;'  --Informix protocol, ex.  onsoctcp
  + N'SERVER= ;'    --Informix server, ex. ifx1
  + N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; --Change to your locale, as needed

EXEC master.dbo.sp_addlinkedserver 
@server = N'UCCX', --Linked Server system name
@srvproduct=N'Ifxoledbc', 
@provider=N'MSDASQL', 
@datasrc=N' ', --Your System DSN Name
@provstr= @provider;

Since you're dealing with a single instance, it should be enough to simply match the values used to create your System DSN. If you need to add additional linked servers for other instances, that can be done following exactly the same format described above without additional DSN entries.

I hope that helps.

Related Question