SQL Server - [分布式查询/事务]

分布式查询

    OPENROWSET

    从Excel取数据

    SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:/1.xls',[Sheet1$])

    从Oracle取数据

    SELECT * FROM OPENROWSET( 'MSDAORA.1', 'NetServiceName';'User';'Password', 'SELECT * FROM OracleTalbe')

 

    Linked Server(for Oracle)

    建立Linked Server

    sp_addlinkedserver 'Allies', 'Oracle', 'MSDAORA.1', 'NetServiceName'

    Oracle的Login

    sp_addlinkedsrvlogin 'Allies', FALSE, 'SQLServerLogin', 'OracleUser', 'OraclePassword'

    从Oracle查询数据

    SELECT*FROM OPENQUERY( Allies, 'SELECT * FROM OracleTalbe')，或者

    SELECT*FROM Allies..OracleUser.OracleTalbe

    修改Linked Server的Server Option

     sp_serveroption'Allies', 'Option Name', 'Option Value'

     例如

    sp_serveroption 'OraDC', 'rpc out', 'true'

    sp_serveroption 'OraDC', 'rpc', 'true'

    向Oracle插入数据（还没有用过）

    INSERT INTO OPENQUERY(Allies, 'SELECT ... FROM OracleTalbe WHERE 1=2') VALUES ( ... )

    删除Linked Server

    sp_dropserver 'Allies', 'droplogins'

 

    注意：使用OPENROWSET、OPENQUERY时，SQL Server不对提交的SQL语句做任何检查，直接将语句提交给Linked Server进行处理；使用四部分命名法时，SQL Server可能从Linked Server上读取被引用表的数据到SQL Server，然后在SQL Server上来完成其它操作。

 

    设置SQL Server到Oracle的Linked Server，可参考以下KB文章：

    How to set up and troubleshoot a linked server to Oracle in SQL Server

    Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider

    Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider

    Supportability of the Microsoft ODBC Driver/ OLE DB Provider for Oracle w.r.t Oracle 8.x

 

    分布式事务中的OPENROWSET、OPENQUERY、OPENDATASOURCE

    如果在事务，或是一些隐含使用事务的情况下，例如TRIGGER中，使用OPENROWSET、OPENQUERY、OPENDATASOURCE时，就需要使用分布式事务来处理。

    在分布式事务中使用OPENROWSET、OPENQUERY、OPENDATASOURCE或Linked Server时，需要注意：

    1. 必须启动MSDTC服务。

    Service Name为Distributed Transaction Coodinator。

    2. 所涉及的Server之间如果存在网关、防火墙，需要开启TCP 135端口。

    分布式事务需要使用这个端口通讯。

    3. 如果Server之间跨网段，则Server之间需要能互相PING到机器名（而不是IP地址）。

    如果相互PING机器名有问题，修改system32/driver/etc目录下的hosts文件。

 

    上面的设置不正确时，会出现类似如下的错误：

    该操作未能执行，因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务。
[OLE/DB provider returned message: 新事务不能登记到指定的事务处理器中。 ]
OLE DB 错误跟踪［OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a］。  

 

    分布式事务可以查询下列KB文章：

    Failed to Enlist on Calling Object's Transaction

    How to troubleshoot MS DTC firewall issues

    You receive error 7391 when you run a distributed transaction against a linked server

 

    使用分布式查询，可以参考：Chapter 25 - Distributed Queries: OLE DB Connectivity。

    关于ORAOLEDB提供程序的详细说明，可以参考： Oracle Provider for OLE DB Developer's Guide - Release 9.2 。