如何从MS SQL通过Linked Server / OpenrowSet / OpenQuery访问Oracle (找到答案！)

xblues

这个问题困扰了我好几天，在公司屡试不爽。最后只好在自己家的环境下安装虚拟机，安装Oracle和MS SQL，终于实验通过！

结论：公司的笨蛋，安装的系统，毛病多多，竟是奇怪的问题，找不出答案的问题。我自己的新环境，一次试验成功！！！

所以说：公司还是要有一个有用网管和DBA，否则真是好事多磨，什么也不行，还这个也不答应，那个也不让你做。

举例：我说需要重启服务器，居然要发邮件，还要发一个工作流请求，然后Team Leader告诉我：等一两周！！！！

举例：我们公司测试服务器升级，居然搞了一个新的服务器名，我被迫更新所有代码设计到路径和机器名的部分！！！原来都是硬件更换，机器名不变，这样一个好处是不需要更改应用的路径，另外一方面，升级不成功，可以来回切换！！！

我无语了！！！！连续加班好几天，居然都是别人的错误，搞死我了。

xblues

从MS Server 配置中打开OpenrowSet的支持，这个缺省是关闭的（安全性考虑），需要在配置中打开这个选项就好，不需要重启服务器也不需要重启SQL服务。

具体看这里：
http://www.kodyaz.com/articles/e ... ibuted-Queries.aspx

两种方法，通过在配置界面中修改设置，或者通过命令行。自己看，命令行修改如下：

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

sp_configure 'show advanced options', 1
reconfigure

xblues

准备一个文本文件，运行这个语句，如果看到结果就说明这个打开了。

SELECT  *
FROM OPENROWSET
       ( BULK 'D:\DataExchange\Input\Batch01.csv',SINGLE_CLOB)
AS a

xblues

安装Oracle客户端

开启Oracle自带的命令行SQL PLUS，输入用户名口令和SID测试，如果测试通过说明，驱动安装没有问题。

查看SQL控制台，Server Objecs \ Linked Servers \ Providers \ 这下边可以看到两个驱动。

MSDAORA   微软的OLE DB驱动
OraOLEED.Oracle    Oracle的OLE DB驱动

两个都好用，哪一个都行！！！当然微软的用起来对自家的系统支持更好。

xblues

SELECT *
FROM OPENROWSET('OraOLEDB.Oracle'
,'orcl';'yourusename';'yourpassword'
, 'select * from all_tables')


orcl 是我的ORA文件里面应用的链接名：（ora文件如下，供你参考）

1. ORCL =
   
2.   (DESCRIPTION =
   
3.     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   
4.     (CONNECT_DATA =
   
5.       (SERVER = DEDICATED)
   
6.       (SERVICE_NAME = orcl.0.2.15)
   
7.     )
   
8.   )

复制代码

你需要一个oracle的用户名和口令，这个用户的权限不需要特殊设置！！！（我原来还以为权限需要特殊设置什么的呢）

最后的那个SQL语句是Oracle的标准语句，查询是所有表信息。

xblues

1. EXEC sp_addlinkedserver   'ls_orcl',  'Oracle',  'OraOLEDB.Oracle',  'orcl'
   
2. EXEC sp_addlinkedsrvlogin 'ls_orcl', 'FALSE',NULL, 'yourusername', 'yourpassword'

复制代码

第一行是建立Linked Server
第二行是给这个Linked Server加入权限


ls_orcl 是你随便起的一个名字，以后就用这个名字引用这个Linked Server了，这个名字没有要求
orcl 同上，是你的ora中的那个oracle服务器的名字
其他的不用解释了吧?

xblues

1. select top 10 * from ls_orcl..HR.JOBS

复制代码

ls_orcl 是你给这个Linked Server起的名字

HR 是Oracle数据库下面的一个Shema

JOBS是HR下面的一个表

你注意到了那个TOP 10 了么？这可是标准的MS SQL语句啊！！！！！爽了吧？
使用LINKED Server 来即使查询太方便了！！！！！

当然不适合做报表或者服务或者数据仓库之类的解决方案（这些应该还用SSIS，SSRS之类的工具或者其他ETL）

但是这个适合自己做数据分析使用，这样Oracle和MS SQL就无缝的链接到一起了！！！！用起来超级方便！

xblues

1. select *
   
2. from OPENROWSET
   
3. ('Microsoft.Jet.OLEDB.4.0'
   
4. ,'Excel 8.0;Database=D:\DataExchange\PersonsContacts.xls;'
   
5. ,'SELECT * FROM [EmployeeeTest$]')

复制代码

可以用来查询各种数据来源的语句，甚至可以更新，最适合处理Ｆｌａｔ　Ｆｉｌｅ文件的导入导出啦！不应该忽视这个功能，这个功能是通过Ｌｉｎｋｅｄ　Ｓｅｒｅｒ包装实现的，不过并不需要显式建立这个　linked server

xblues

建立Linked Server
http://support.microsoft.com/kb/240340

Oracle连接问题纠错
http://support.microsoft.com/default.aspx?kbid=259959

Oracle版本问题纠错
http://support.microsoft.com/default.aspx?kbid=280106

很有用的讨论
http://bytes.com/topic/sql-serve ... set-drivers-msdaora

xblues

在公司加班到半夜11点回家，搞定了所有SSIS和SSRS的异构数据库的连接

在家接着加班，搞定了Openrowset 和 Linked Server的建立！

臭骂我们公司的IT。。。。。。。。。。。。&*(%^&$(^&%&^&^%%&%%^%^$$E$%$%$%$^%^ 一群笨蛋！

我下一步的问题是怎么让这群笨蛋帮我。。。。。。。。。。。

还要研究如何同步那些笨蛋系统的数据（因为设计问题，同步也是一个难题，没有主键，也没有更新时间戳！！！！！ ）

ubuntuhk

虫子，你咋这么拼命啊？你就等你们公司的tech support一两周后给你fix problem呗，休息一下

xblues

我也不想这样啊，可是我接手的这个小姑娘一直用她的方法照顾日常事务，正常工作。她马上走了，我有两天路可以选择：接着她的方法干活，自己研究改进方法。我每天都跟着她学习，不过我更想改进。

因为她的实在是。。。。。。。。。根本就是没有过站在理论看过这些问题。（当然我不得不承认人家干活利索）

xblues

我实验的时候使用的是Oracle的样例数据库和样例用户HR，不知道是否和这个有关系。
为了排除这种可能我需要重新建立一个新用户，重新实验一下。

谁知道那个HR用户的权限，请告诉我一下，好么？我对Ｏｒａｃｌｅ不熟悉，刚才有进入不了Ｏｒｃｌｅｗｅｂ管理界面了。

coredump

sqlplus / as sysdba
grant all privileges to hr;

xblues

could you help me to take a look at the user HR and find out what sort of access does this user have? So I can compare this with my user account from my company.

coredump

原帖由 xblues 于 19-8-2010 09:55 发表

                               
登录/注册后可看大图

could you help me to take a look at the user HR and find out what sort of access does this user have? So I can compare this with my user account from my company.

每次装完ORACLE后，我都是GRANT ALL PRIVILEGES TO HR;所以没有默认的权限可以看，默认情况下HR就是普通用户权限，应该和你直接CREATE USER出来的帐号的权限相同。

雅瑶冬月

原帖由 xblues 于 19-8-2010 02:13 发表

                               
登录/注册后可看大图

这个问题困扰了我好几天，在公司屡试不爽。最后只好在自己家的环境下安装虚拟机，安装Oracle和MS SQL，终于实验通过！

结论：公司的笨蛋，安装的系统，毛病多多，竟是奇怪的问题，找不出答案的问题。我自己的新环 ...

举例：我说需要重启服务器，居然要发邮件，还要发一个工作流请求，然后Team Leader告诉我：等一两周！！！！
大哥，bounce a production server，不是一台workstation，要走流程是肯定的，保护公司，主要是保护自己，永远不要以为简单的重启不会有什么问题，莫菲定律大家都知道的。
举例：我们公司测试服务器升级，居然搞了一个新的服务器名，我被迫更新所有代码设计到路径和机器名的部分！！！原来都是硬件更换，机器名不变，这样一个好处是不需要更改应用的路径，另外一方面，升级不成功，可以来回切换！！！
我会建议用DNS，把你要访问的服务器在DNS上设一条entry，比如 ClientDB.xyz.biz之类的，然后所有程序用到这个服务的时候只用DNA Entry，将来再有任何其他的改动（机器名，IP地址等等），只需改动DNS就可以了。。。

雅瑶冬月

原帖由 xblues 于 19-8-2010 02:48 发表

                               
登录/注册后可看大图

在公司加班到半夜11点回家，搞定了所有SSIS和SSRS的异构数据库的连接

在家接着加班，搞定了Openrowset 和 Linked Server的建立！

臭骂我们公司的IT。。。。。。。。。。。。&*(%^&$(^&%&^&^%%&%%^%^$$E$%$%$%$ ...

没有Pk，没有办法同步啊。。。

雅瑶冬月

如果是MS SQL相关的话，这个论坛有不少有用得东东。

http://www.sqlservercentral.com/

nowaybutgo

http://www.freeoz.org/ibbs/viewt ... e%3D1&frombbs=1

各位前辈， 请大伙发表发表看法吧，谢谢啦！

i2i2

快成DBa版了