【SQLServer】使用T-SQL访问远程数据库：openrowset 和 openquery 以及连接服务器的创建

█ 启用/关闭Ad Hoc Distributed Queries
Ad Hoc Distributed Queries服务默认是关闭的，要使用openrowset 和 openquery访问远程数据库，需要在本地启用该服务

 

-- 启用    Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1 
reconfigure 
exec sp_configure 'Ad Hoc Distributed Queries',1 
reconfigure 

-- 关闭 Ad Hoc Distributed Queries
exec sp_configure 'Ad Hoc Distributed Queries',0 
reconfigure 
exec sp_configure 'show advanced options',0 
reconfigure

 

█openrowset 和 openquery访问远程数据库

1、openquery

【首先需要创建服务器连接】
方法1. SQLServerManagementStudio→服务器对象→连接服务器，右键（新建连接服务器），录入服务器名，
          选择服务器类型：SQLServer或其他数据源
            （Oracle：Microsoft OLE DB Provide for Oracle）
            （SQL Server：Microsoft OLE DB Provide for SQL Server）
            （Sybace等：Microsoft OLE DB Provide for ODBC Drivers）
          安全性→使用此安全上下文建立连接：远程数据库用户名及密码
          服务器选项：如果要访问远程数据库中的存储过程，RPC/RPC OUT：ture

方法2. 通过执行以下命令行实现：
          创建后在SQLServerManagementStudio→服务器对象→连接服务器看到新创建的连接服务器，RPC/RPC OUT需要手动修改

 

--创建链接服务器 
exec sp_addlinkedserver   '连接服务器名称', '', 'SQLOLEDB', '服务器IP地址' 
exec sp_addlinkedsrvlogin '连接服务器名称', 'false',null, '用户名', '密码' 

--删除链接服务器 
exec sp_dropserver  '连接服务器名称', 'droplogins' 

【查询】

-- 无参数查询（IPSERVER为连接服务器名称）
select * from openquery(IPSERVER,'select * from TB.dbo.employee')

-- 有参数查询（IPSERVER为连接服务器名称）
declare @v_sql varchar(4000)
declare @v_employeeID varchar(5)
select @v_employeeID = '100001'
set @v_sql = 'select *  from openquery(IPSERVER,''select * from TB.dbo.employee where employeeID = ''''' + @v_employeeID + ''''''') as a'
exec (@v_sql)

【调用存储过程及函数】

declare @v_btime datetime
declare @v_etime datetime
declare @v_sql varchar(4000)
select @v_btime = '2013.07.01'
select @v_etime = '2013.07.02'

-- 有参数调用函数（IPSERVER为连接服务器名称）
SET @v_sql = 'select * from openquery(IPSERVER, ''select * from TB.dbo.func_OrderDetail('''''+convert(varchar(19),@v_btime,120)+''''','''''+convert(varchar(19),@v_etime,120)+''''')'')'
exec (@v_sql)
        
-- 有参数调用存储过程（IPSERVER为连接服务器名称）
SET @v_sql = 'select * from openquery(IPSERVER, ''exec TB.dbo.proc_OrderDetail '''''+convert(varchar(19),@v_btime,120)+''''','''''+convert(varchar(19),@v_etime,120)+''''''')'
exec (@v_sql)

 

2、openrowset

【查询】

-- 无参数查询（远程SQLServer服务器）
select *  from openrowset('MSDASQL', 'DRIVER={SQL Server};SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs', 'select * from TB.dbo.employee') as a

select *  from openrowset('SQLOLEDB', 'DRIVER={SQL Server};SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs', 'select * from TB.dbo.employee') as a

select *  from openrowset('SQLOLEDB', 'SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs', 'select * from TB.dbo.employee') as a


-- 有参数查询（远程SQLServer服务器）
declare @v_sql varchar(4000)
declare @v_employeeID varchar(5)
select @v_employeeID = '10001'
set @v_sql = 'select *  from openrowset(''MSDASQL'', ''DRIVER={SQL Server};SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs'', ''select * from TB.dbo.employee where employeeID = ''''' + @v_employeeID + ''''''') as a'
exec (@v_sql)

declare @v_sql varchar(4000)
declare @v_employeeID varchar(5)
select @v_employeeID = '10001'
set @v_sql = 'select *  from openrowset(''SQLOLEDB'', ''DRIVER={SQL Server};SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs'', ''select * from TB.dbo.employee where employeeID = ''''' + @v_employeeID + ''''''') as a'
exec (@v_sql)

declare @v_sql varchar(4000)
declare @v_employeeID varchar(5)
select @v_employeeID = '10001'
set @v_sql = 'select *  from openrowset(''SQLOLEDB'', ''SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs'', ''select * from TB.dbo.employee where employeeID = ''''' + @v_employeeID + ''''''') as a'
exec (@v_sql)



-- 无参数查询（远程Oracle服务器）
select *  from openrowset('MSDAORA','实例名';'用户名';'密码','select * from TB.employee') as a

-- 有参数查询（远程Oracle服务器）
declare @v_sql varchar(4000)
declare @v_employeeID varchar(5)
select @v_employeeID = '10001'
set @v_sql = 'select *  from openrowset(''MSDAORA'', ''实例名'';''用户名'';''密码'', ''select * from TB.employee where employeeId = ''''' + @v_employeeID + ''''''') as a'
exec (@v_sql)

【调用存储过程和函数】

declare @v_btime datetime
declare @v_etime datetime
declare @v_sql varchar(4000)
select @v_btime = '2013.07.01'
select @v_etime = '2013.07.02'

-- 调用函数
set @v_sql = 'select * from openrowset(''MSDASQL'', ''DRIVER={SQL Server};SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs'', ''select * from TB.dbo.func_OrderDetail('''''+convert(varchar(19),@v_btime,120)+''''','''''+convert(varchar(19),@v_etime,120)+''''')'') as a'
exec (@v_sql)

-- 调用存储过程
set @v_sql = 'select * from openrowset(''MSDASQL'', ''DRIVER={SQL Server};SERVER=服务器IP地址;UID=用户名;PWD=密码;Initial Catalog=Pubs'', ''exec TB.dbo.proc_OrderDetail '''''+convert(varchar(19),@v_btime,120)+''''','''''+convert(varchar(19),@v_etime,120)+''''''') as a'
exec (@v_sql)