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.