SqlServer分布式查询

 

 

说明：本文连接两个远程数据库

类型	服务器	数据库	表名
Sql Server	SGPTIEDBDEV01	ChinaODSTest20110706	Interface_Account_DIMN

访问SqlServer的用户名：MDMTST 密码：MDMTST

 

类型	主机名	服务名	本地服务名	表名
Oracle	EMAEDCS062.pfizer.com	edmp1.sandwich.pfizer.com	EDW	RDM_POSITION_DIMN_VW

访问Oracle的用户名：WANGX23 密码：firewas#245

创建For Oracle的Linked Server，需要安装Oracle客户端，并创建本地服务。

本文测试的前提是安装了Oracle Client 9.2.0.8，并创建了本地服务EDW。

创建Oracle本地服务的步骤参见正文。

一、  创建Oracle本地服务

program files-->Oracle-OraHome92-->Configuration and Magration tools-->Net Manager-->将弹出Oracle Net Manager窗口-->选择Oracle Net配置-->选择本地-->选择服务命名-->点击左上角的加号图标-->将出现Net 服务名向导-->在Net服务名处输入"EDW"并点击下一步-->选择"TCP/IP(Internet协议)"并点击下一步-->在主机名处输入"EMAEDCS062.pfizer.com"，端口为默认的"1521"，点击下一步-->在(Oracle8i或更高版本)服务名处输入"edmp1.sandwich.pfizer.com",连接类型为"数据库默认"，点击下一步-->点击测试按钮-->更改登录-->输入用户名：WANGX23 口令：firewas#245进行测试，测试成功会显示"连接测试成功"-->点击关闭按钮-->点击完成按钮。

二、LINKED SERVER

1．   建立Linked Server

（1）       命令方式

First: For SqlServer

EXEC master.dbo.sp_addlinkedserver @server = N'SGPTIEDBDEV01', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SGPTIEDBDEV01',@useself=N'False',@locallogin=NULL,@rmtuser=N'MDMTST',@rmtpassword=N'MDMTST'

Second: For Oracle

EXEC master.dbo.sp_addlinkedserver @server = N'EDW', @srvproduct=N'Oracle', @provider=N'MSDAORA', @datasrc=N'EDW', @provstr=N'MSDAORA'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EDW',@useself=N'False',@locallogin=NULL,@rmtuser=N'WANGX23',@rmtpassword=N'firewas#245'

（2）       可视化方式

First: For SqlServer

Server Objects-->Linked Server-->右键-->New Linked Server-->在New Linked Server窗口左侧选择General-->

在右侧Linked Server处输入"SGPTIEDBDEV01"-->选择Server Type为SQL Server-->在New Linked Server窗口左侧选择Security

-->在右侧下方选择"Be made using this security context"-->在Remote login处输入"MDMTST"-->在With password处输入"MDMTST"-->点击OK完成

Second: For Oracle

Server Objects-->Linked Server-->右键-->New Linked Server-->在New Linked Server窗口左侧选择General-->

在右侧Linked Server处输入"EDW"-->选择Server Type为Other data source-->选择Provider为"Microsoft OLE DB Provider for Oracle"-->在Product name处输入"Oracle"-->在Data source处输入"EDW"-->在Provider string处输入"MSDAORA"

--在New Linked Server窗口左侧选择Security-->在右侧下方选择"Be made using this security context"-->在Remote login处输入"WANGX23"-->在With password处输入"firewas#245"-->点击OK完成

2．   使用Linked Server进行查询

（1）       查询SqlServer

方式1：

SELECT * FROM OPENQUERY(SGPTIEDBDEV01,

'SELECT COUNT(*) FROM ChinaODSTest20110706.DBO.Interface_Account_DIMN')

方式2：

SELECT COUNT(*) FROM SGPTIEDBDEV01.ChinaODSTest20110706.DBO.Interface_Account_DIMN

（2）       查询Oracle

示例1：

SELECT * FROM OPENQUERY(EDW,

'SELECT COUNT(*) FROM RDM_POSITION_DIMN_VW')

示例2：

                                     SELECT * FROM OPENQUERY

(EDW,'

    SELECT     ACCT_ID_SK,

              ACCT_ID_NK,

              ACCT_NM             

    FROM       rdm_account_dimn_vw 

    WHERE      ROWNUM < 100')

三、OPENDATASOURCE

1.       基本语法

SELECT * FROM OPENDATASOURCE('provider_name','init_string').databasename.dbo.tablename

2.  更多语法

参见：http://msdn.microsoft.com/zh-cn/library/ms179856.aspx

3.       示例

（1）   查询SqlServer

SELECT TOP 100 * FROM OPENDATASOURCE('SQLOLEDB','DATA SOURCE=SGPTIEDBDEV01;USER ID=MDMTST;PASSWORD=MDMTST;').ChinaODSTest20110706.DBO.Interface_Account_DIMN

（2）       查询Oracle

 

四、OPENROWSET

1.       基本语法

SELECT * FROM OPENROWSET('provider_name','datasource';'user_id';'password',databasename.dbo.tablename)

2.       更多语法

http://msdn.microsoft.com/zh-cn/library/ms190312.aspx

3.       示例

（1）       查询SqlServer

SELECT TOP 100 * FROM OPENROWSET('SQLOLEDB','SGPTIEDBDEV01';'MDMTST';'MDMTST',ChinaODSTest20110706.DBO.Interface_Account_DIMN)

（2）       查询Oracle

 

五、异常

         在使用OPENROWSET/OPENDATASOURCE时，可能会有以下异常。

    异常描述：

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

解决办法：

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 0;

GO

RECONFIGURE;

GO