[SQL Server 2005] How to create Linked Server targeting remote ODBC?

Question

I have a WebTrends ODBC source on server1 and a SQL Server 2005 on server2. I want to connect to this ODBC from SQL Server on server2. So far, I managed to connect to this ODBC from SQL Server which is on same server, using following definition for linked server:

EXEC sp_addlinkedserver 
@server = N'WT_ODBC', 
@provider=N'MSDASQL', 
@datasrc=N'WT_ODBC_test'

EXEC sp_addlinkedsrvlogin 
@rmtsrvname=N'WT_ODBC',
@useself=N'False',
@locallogin=N'sa',
@rmtuser=N'administrator',
@rmtpassword='########'

I can query this linked server like this:

SELECT *
FROM OPENQUERY(WT_ODBC, 'SELECT * FROM CompleteViewV85.DownloadedFiles');

BTW, I can't query it with four-part name like this:

SELECT *
FROM WT_ODBC.[Complete View V8.5].CompleteViewV85.DownloadedFiles

I receive the following error:

Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server   
"WT_ODBC". A four-part name was supplied, but the provider does not expose the  
necessary   interfaces to use a catalog or schema.

..and I verified that option "Level zero only" is not checked. But that is not so problematic, since the first query works.

My problem is - how to connect to this ODBC but from another server? I went on server2, and there in SQL Server I've created the following linked server:

EXEC sp_addlinkedserver 
@server = N'WT_ODBC', 
@srvproduct=N'Microsoft OLE DB Provider for ODBC', 
@provider=N'MSDASQL', 
@datasrc=N'WT_ODBC_test',
@location=N'10.254.251.20'

EXEC sp_addlinkedsrvlogin 
@rmtsrvname=N'WT_ODBC',
@useself=N'False',
@locallogin=N'sa',
@rmtuser=N'administrator',
@rmtpassword='########'

..where I placed the IP address of server1 as @location parameter. Linked server that is created this way can't connect to ODBC on server1. When I try to run the same query that works on server1, I get the following error:

Cannot get the column information from OLE DB provider "MSDASQL" for linked server  
"WT_ODBC".

Help? Someone? Please? :)

Thanks in advance.

Answer 1

Does it work from ODBC sources in windows, when you test connection? In Administrative Tools\Data Sources (ODBC)

BTW, OPENQUERY is a good way to get data over from "unusual" (for the lack of better word) sources, because the query is pass-through. The query command is sent over to the remote server, executed over there and only results are returned back.

If you do not use open query, it depends on optimizer. If remote server is SQL server, DB2, Oracle, the optimizer will figure it out. If it is something unusual, it may decide to start moving all table data over and then start column and row filtering on local machine -- way slower.

Answer 2

I'd say that don't use @location parameter, but instead configure your WT_ODBC_test ODBC data source properly. You need to configure it on server2 of course.