如何创建针对远程 ODBC 的链接服务器?
我在 server1 上有一个 WebTrends ODBC 源,在 server2 上有一个 SQL Server 2005。我想从 server2 上的 SQL Server 连接到此 ODBC。到目前为止,我设法从位于同一服务器上的 SQL Server 连接到此 ODBC,使用链接服务器的以下定义:
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='########'
我可以像这样查询此链接服务器:
SELECT *
FROM OPENQUERY(WT_ODBC, 'SELECT * FROM CompleteViewV85.DownloadedFiles');
顺便说一句,我无法使用这样的四部分名称来查询它:
SELECT *
FROM WT_ODBC.[Complete View V8.5].CompleteViewV85.DownloadedFiles
我收到以下错误:
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.
..并且我验证未选中选项“仅零级”。 但这并不是什么问题,因为第一个查询有效。
我的问题是 - 如何从另一台服务器连接到此 ODBC? 我在 server2 上,在 SQL 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='########'
..我将 server1 的 IP 地址放置为 @location 参数。 以这种方式创建的链接服务器无法连接到 server1 上的 ODBC。当我尝试运行在 server1 上运行的相同查询时,出现以下错误:
Cannot get the column information from OLE DB provider "MSDASQL" for linked server
"WT_ODBC".
帮助?有人吗?请? :)
提前致谢。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我想说不要使用
@location
参数,而是正确配置WT_ODBC_test
ODBC 数据源。当然你需要在server2上配置它。I'd say that don't use
@location
parameter, but instead configure yourWT_ODBC_test
ODBC data source properly. You need to configure it on server2 of course.当您测试连接时,它是否可以从 Windows 中的 ODBC 源工作?在
Administrative Tools\Data Sources (ODBC)
顺便说一句,
OPENQUERY
是从“不寻常”(因为缺乏更好的词)源获取数据的好方法,因为查询是传递的。查询命令被发送到远程服务器,在那里执行,并且只返回结果。如果不使用开放查询,则取决于优化器。如果远程服务器是 SQL Server、DB2、Oracle,优化器会计算出来。如果出现异常情况,它可能会决定开始移动所有表数据,然后在本地计算机上启动列和行过滤——速度要慢得多。
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.