【SQL SERVER】OPENQUERY远程函数引用及参数传值问题 - 简书
下载简书移动应用
写了12888字，被50人关注，获得了134个喜欢
【SQL SERVER】OPENQUERY远程函数引用及参数传值问题
两台服务器，都安装有数据库，服务器A,服务器B。服务器B的数据库上配置有指向服务器A的数据库的链接数据库。服务器A的数据库Test里创建以下两个函数，一个函数返回一个表值，一个返回单个字符串值。
CREATE FUNCTION FUN_ReturnTable
@para VARCHAR(50)
RETURNS @table TABLE(
col VARCHAR(50)
INSERT INTO @table
( 'This is Test.'),
CREATE FUNCTION FUN_ReturnValue (@para VARCHAR(50)) RETURNS varchar(50)
RETURN ('Input para is '+@para)
服务器A上的数据库可以执行
SELECT * FROM
Test.dbo.FUN_ReturnTable('haha') --返回表
SELECT dbo.FUN_ReturnValue('haha') --返回单个值
然后在服务器B上的数据库运行以下语句调用（假设配置的链接服务器为LS）
SELECT LS.Test.dbo.FUN_ReturnValue('haha')
于是就报错了:"不允许远程函数引用 'LS.Test.dbo.FUN_ReturnValue'，找不到列名 'LS'，或者列名不明确。"
可以看到这样直接通过链接数据库调用是不行的。需要换个方式，改用OPENQUERY。
DECLARE @a VARCHAR(50)
set @a = (select * from openquery(LS,'select Test.dbo.FUN_ReturnValue(''haha'')'))
select @a --返回的字符串值
SELECT * from openquery(LS,'select * from Test.dbo.FUN_ReturnTable(''haha'')') --返回的表值
以上的调用，函数的参数值是写死的，如果传一个动态参数，改成下面这样：
DECLARE @a VARCHAR(50)
DECLARE @b VARCHAR(50)
set @a = (select * from openquery(LS,'select Test.dbo.FUN_ReturnValue('''+@b+''')'))
是无法运行的，提示错误：
SQL-SERVER-RPC-FUNCTION-CALL-1
这个问题我就费解了，OPENQUERY的第二个参数不能直接带有动态变量，不知道啥原因，官网上也备注说明不可以含有参数。
解决方法是换种方式调用，改用EXEC的方法。
DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
set @b = 'haha';
set @sql = 'select a from openquery(LS,''select Test.dbo.FUN_ReturnValue('''''+@b+''''') AS a'')';
exec(@sql);
以上语句是可以执行的，输出“Input para is haha”单行的字符串。那么现在问题来了，我想将现在输出的结果赋值给一个本地变量@a，所以想当然就这样写
DECLARE @a VARCHAR(50)
DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
set @b = 'haha';
set @sql = 'select a from openquery(LS,''select Test.dbo.FUN_ReturnValue('''''+@b+''''') AS a'')';
set @a = exec(@sql); --是错误的，语法错误
set @a = (select * from exec(@sql)); --是错误的，语法错误
select @a;
以上给@a变量赋值方法都不行。于是将@a改为表变量。
DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
DECLARE @a table( name varchar(50))
set @b = 'haha';
set @sql = 'select a from openquery(LS,''select Test.dbo.FUN_ReturnValue('''''+@b+''''') AS a'')';
insert into @a exec(@sql); --将结果插入表里
select name from @a
这样结果就保存到表变量了。调用返回表值的函数也是可以的。
DECLARE @b VARCHAR(50)
DECLARE @sql varchar(500)
DECLARE @a table( name varchar(50))
set @b = 'haha';
set @sql = 'select * from openquery(LS,''select * from Test.dbo.FUN_ReturnTable('''''+@b+''''') AS a'')'; --调用表值函数。
insert into @a exec(@sql);
select name from @a
1.SQL SERVER通过链接服务器是可以调用远程数据库的存储过程的。
通过 exec LS.Test.dbo.testSP 'haha' 的方式，其中LS是链接服务器，Test是数据库名称。但是这种方式需要将链接服务器配置为用户RPC。
通过 select * from openquery(LS,'exec Test.dbo.testSP ''haha'' ')的方式,其中LS是链接服务器，Test是数据库名称。
2.SQL SERVER通过链接服务器远程调用函数不能通过 LS.Test.dbo.testFun('haha')的方式直接调用，改用OPENQUERY的方式。
3.OPENQUERY的第二个参数不能含有参数变量。如果需要传递参数，改成拼接字符串的形式，EXEC 命令执行字符串。
如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作！
打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮
被以下专题收入，发现更多相似内容：
如果你是程序员，或者有一颗喜欢写程序的心，喜欢分享技术干货、项目经验、程序员日常囧事等等，欢迎投稿《程序员》专题。
专题主编：小...
· 142647人关注
玩转简书的第一步，从这个专题开始。
想上首页热门榜么？好内容想被更多人看到么？来投稿吧！如果被拒也不要灰心哦～入选文章会进一个队...
· 122366人关注
关注互联网、科技。
**诚邀科技爱好者一起管理这个专题，私信我。
不定期会精选一些好的文章发在公众号（joojencom）...
· 27332人关注
如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作！
选择支付方式：Access VBA教程：OpenQuery方法
Access VBA教程：OpenQuery方法
- OpenQuery方法
Access VBA教程：OpenQuery方法
在 Visual Basic 中，OpenQuery方法执行 操作。
expression.OpenQuery(QueryName, View, DataMode)
expression&& 必需。返回“Applies To”列表中的一个对象的表达式。QueryName&&必需 Variant 型。字符串表达式，表示当前数据库中查询的有效名称。如果在某类库数据库中使用 OpenQuery方法的 Visual Basic 代码，Microsoft Access 将先在该类库数据库中搜索具有该名称的查询，然后再在当前数据库中搜索。
View&&可选AcView。
AcView 可以是下列 AcView 常量之一：
acViewDesign
acViewNormal 默认
acViewPivotChart
acViewPivotTable
acViewPreview
如果 queryname参数是属性设为 -1 的选择查询、交叉表查询、联合查询或传递查询的名称，则 acViewNormal 将显示查询的结果集。如果 queryname参数引用的是 ReturnsRecords属性设为 0 的操作查询、数据定义查询或传递查询，则 acViewNormal 将执行查询。
如果将该参数留空，将采用默认常量 (acViewNormal)。
DataMode&&可选AcOpenDataMode。
AcOpenDataMode 可以是下列 AcOpenDataMode 常量之一：
acEdit 默认
acReadOnly
如果将该参数留空，将采用默认常量 (acEdit)。
有关该操作及其参数如何使用的详细信息，请参阅该操作的主题。
注意&& 该方法仅可用于Microsoft Access 数据库环境 (.mdb)。如果使用 MicrosoftAccess 项目环境 (.adp)，请参阅 或方法。
如果指定 datamode参数，并将 view参数留空，那么必须包含 view参数的逗号。如果将末端的参数留空，则在指定的最后一个参数后面不需使用逗号。
下面的示例在“数据表”视图中打开“销售额总计查询”，使用户能够查看，但是不能编辑或添加记录。
DoCmd.OpenQuery "Sales Totals Query", , acReadOnly
上页： 下页：
Access VBA教程：OpenQuery方法
- OpenQuery方法
版权所有 & 　证书：粤ICP备号博客访问： 244104
博文数量： 130
博客积分： 3370
博客等级： 中校
技术积分： 1335
注册时间：
Just a blog
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： 数据库开发技术
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 :34
-- Copyright (c)
Microsoft Corporation
-- Developer Edition on Windows NT 5.1
(Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258（转载保留此信息）
-- Subject: SQL不同服务器数据库之间数据操作整理
----------------------------------------------------------------------------------
--1. 创建链接服务器
--1.1 创建一个链接名
EXEC sp_addlinkedserver 'LinkName','','SQLOLEDB','远程服务器名或ip地址' --有自定义实例名还要加上"\实例名"
/*例如：EXEC sp_addlinkedserver 'TonyLink','','SQLOLEDB','192.168.58.208' */
--1.2 创建登录信息(或叫创建链接服务器登录名映射)(只需选择一种方式)
--1.2.1 以windows认证的方式登录
EXEC sp_addlinkedsrvlogin 'LinkName' --或EXEC sp_addlinkedsrvlogin 'LinkName','true'
/*例如：EXEC sp_addlinkedsrvlogin 'TonyLink' */
--1.2.2 以SQL认证的方式登录
EXEC sp_addlinkedsrvlogin 'LinkName','false',NULL,'用户名','密码'
/*例如：EXEC sp_addlinkedsrvlogin 'TonyLink','false',null,'sa','123' */
--2. 链接服务器相关数据操作
--2.1 查询示例
SELECT * FROM LinkName.数据库名.架构名.表名
/*例如：SELECT * FROM TonyLink.Mydb.dbo.tb */
--2.2 导入示例
SELECT * INTO 表名 FROM LinkName.数据库名.架构名.表名
/*例如：SELECT * INTO Newtb FROM TonyLink.Mydb.dbo.tb */
--2.3 更新示例
UPDATE LinkName.数据库名.架构名.表名 SET 字段='值' WHERE 字段='条件'
/*例如：UPDATE TonyLink.Mydb.dbo.tb SET Persons='g' WHERE Persons='a' */
--2.4 删除示例
DELETE LinkName.数据库名.架构名.表名 WHERE 字段名='条件'
/*例如：DELETE TonyLink.Mydb.dbo.tb WHERE Persons='g' */
--3. 通过行集函数(OPENQUERY/OPENROWSET/OPENDATASOURCE)操作方法
--3.1 OPENQUERY 方法（需要借助刚创建的链接服务器）：
--3.1.1 查询示例
SELECT * FROM OPENQUERY(LinkName,'SELECT * FROM 数据库名.架构名.表名')
/* 例如：SELECT * FROM OPENQUERY(TonyLink,'SELECT * FROM Mydb.dbo.tb') */
--3.1.2 导入示例
--3.1.2.1 导入所有列
INSERT OPENQUERY(LinkName, 'SELECT * FROM 数据库名.架构名.表名') SELECT * FROM 本地表
/* 例如：INSERT OPENQUERY(TonyLink,'SELECT * FROM Mydb.dbo.tb') SELECT * FROM tb */
--3.1.2.2 导入指定列
INSERT OPENQUERY(LinkName, 'SELECT * FROM 数据库名.架构名.表名') (列,列...)
SELECT 列,列... FROM 本地表
/* 例如：INSERT OPENQUERY(TonyLink,'SELECT * FROM Mydb.dbo.tb')(RANGE,LEVEL,Persons)
SELECT RANGE,LEVEL,Persons FROM tb
--3.1.3 更新示例
UPDATE OPENQUERY(LinkName, 'SELECT * FROM 数据库名.架构名.表名') SET 字段='值' WHERE 字段='条件'
/*例如：UPDATE OPENQUERY(TonyLink, 'SELECT * FROM Mydb.dbo.tb') SET Persons='g' WHERE Persons='a' */
--3.1.4 删除示例
DELETE OPENQUERY(LinkName, 'SELECT * FROM 数据库名.架构名.表名') WHERE 字段名='条件'
/*例如：DELETE OPENQUERY(TonyLink, 'SELECT * FROM Mydb.dbo.tb') WHERE Persons='g' */
--3.2 OPENROWSET方法(不需要用到创建好的链接名。如果连接的实例名不是默认的，需要在"sql服务器名或IP地址"后加上"\实例名")
--3.2.1 查询示例
--3.2.1.1 Windows认证方式查询(以下方法之一即可)
SELECT * FROM OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes',数据库名.架构名.表名)
SELECT * FROM OPENROWSET('SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes',数据库名.架构名.表名)
SELECT * FROM OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes','SELECT * FROM 数据库名.架构名.表名')
SELECT * FROM OPENROWSET('SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes','SELECT * FROM 数据库名.架构名.表名')
/* 例如：SELECT * FROM OPENROWSET('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb)
或：SELECT * FROM OPENROWSET('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb)
或：SELECT * FROM OPENROWSET('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes','SELECT * FROM Mydb.dbo.tb')
或：SELECT * FROM OPENROWSET('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes','SELECT * FROM Mydb.dbo.tb')
--3.2.1.2 SQL认证方式查询(以下方法之一即可)
SELECT * FROM OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
SELECT * FROM OPENROWSET('SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
SELECT * FROM OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码','SELECT * FROM 数据库名.架构名.表名')
SELECT * FROM OPENROWSET('SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码','SELECT * FROM 数据库名.架构名.表名')
SELECT * FROM OPENROWSET('SQLOLEDB', 'sql服务器名';'用户名'; '密码',数据库名.架构名.表名)
SELECT * FROM OPENROWSET('SQLNCLI', 'sql服务器名';'用户名'; '密码',数据库名.架构名.表名)
SELECT * FROM OPENROWSET('SQLOLEDB', 'sql服务器名';'用户名'; '密码','SELECT * FROM 数据库名.架构名.表名')
SELECT * FROM OPENROWSET('SQLNCLI', 'sql服务器名';'用户名'; '密码','SELECT * FROM 数据库名.架构名.表名')
/* 例如：SELECT * FROM OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=pwd=123',mydb.dbo.tb)
或：SELECT * FROM OPENROWSET('SQLNCLI','server=192.168.58.208;uid=pwd=123',mydb.dbo.tb)
或：SELECT * FROM OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=pwd=123','SELECT * FROM Mydb.dbo.tb')
或：SELECT * FROM OPENROWSET('SQLNCLI','server=192.168.58.208;uid=pwd=123','SELECT * FROM Mydb.dbo.tb')
或：SELECT * FROM OPENROWSET('SQLOLEDB','192.168.58.208';'sa';'123',mydb.dbo.tb)
或：SELECT * FROM OPENROWSET('SQLNCLI','192.168.58.208';'sa';'123',mydb.dbo.tb)
或：SELECT * FROM OPENROWSET('SQLOLEDB','192.168.58.208';'sa';'123','SELECT * FROM Mydb.dbo.tb')
或：SELECT * FROM OPENROWSET('SQLNCLI','192.168.58.208';'sa';'123','SELECT * FROM Mydb.dbo.tb')
--3.2.2 导入示例
--3.2.2.1 导入所有列
INSERT OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
SELECT * FROM 本地表
/* 例如：INSERT OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=pwd=123',mydb.dbo.tb)
SELECT * FROM tb
--3.2.2.2 导入指定列
INSERT OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)(列,列...)
SELECT 列,列... FROM 本地表
/* 例如：INSERT OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=pwd=123',mydb.dbo.tb)(RANGE,LEVEL,Persons)
SELECT RANGE,LEVEL,Persons FROM tb
--注：更多替代方法参考.2.1查询示例，只需替换行集函数(OPENROWSET)内的内容即可。
--3.2.3 更新示例
UPDATE OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
SET 字段='值'
WHERE 字段='条件'
/*例如：UPDATE OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=pwd=123',mydb.dbo.tb)
SET Persons='g'
WHERE Persons='a'
--注：更多替代方法参考.2.1查询示例，只需替换行集函数(OPENROWSET)内的内容即可。
--3.2.4 删除示例
DELETE OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)
WHERE 字段名='条件'
/*例如：DELETE OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=pwd=123',mydb.dbo.tb)
WHERE Persons='g'
--注：更多替代方法参考.2.1查询示例，只需替换行集函数(OPENROWSET)内的内容即可。
--3.3 OPENDATASOURCE方法(不需要用到创建好的链接名。如果连接的实例名不是默认的，需要在"sql服务器名或IP地址"后加上"\实例名")
--3.3.1 查询示例
--3.3.1.1 Windows认证方式查询(以下方法之一即可)
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes').数据库名.架构名.表名
/* 例如：SELECT * FROM OPENDATASOURCE('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes').Mydb.dbo.tb
或：SELECT * FROM OPENDATASOURCE('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes').Mydb.dbo.tb
--3.3.1.2 SQL认证方式查询(以下方法之一即可)
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
/* 例如：SELECT * FROM OPENDATASOURCE('SQLOLEDB','Server=192.168.58.208;uid=pwd=123').mydb.dbo.tb
或：SELECT * FROM OPENDATASOURCE('SQLNCLI','Server=192.168.58.208;uid=pwd=123').mydb.dbo.tb
或：SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.58.208;uid=pwd=123').mydb.dbo.tb
或：SELECT * FROM OPENDATASOURCE('SQLNCLI','Data source=192.168.58.208;uid=pwd=123').mydb.dbo.tb
--3.3.2 导入示例
--3.3.2.1 导入所有列
INSERT OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SELECT * FROM 本地表
/* 例如：INSERT OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=pwd=123').mydb.dbo.tb
SELECT * FROM tb
--3.3.2.2 导入指定列
INSERT OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名(列,列...)
SELECT 列,列... FROM 本地表
/* 例如：INSERT OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=pwd=123').mydb.dbo.tb(RANGE,LEVEL,Persons)
SELECT RANGE,LEVEL,Persons FROM tb
--注：更多替代方法参考.3.1查询示例，只需替换行集函数(OPENDATASOURCE)内的内容即可。
--3.3.3 更新示例
UPDATE OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
SET 字段='值'
WHERE 字段='条件'
/*例如：UPDATE OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=pwd=123').mydb.dbo.tb
SET Persons='g'
WHERE Persons='a'
--注：更多替代方法参考.3.1查询示例，只需替换行集函数(OPENDATASOURCE)内的内容即可。
--3.3.4 删除示例
DELETE OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名
WHERE 字段名='条件'
/*例如：DELETE OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=pwd=123').mydb.dbo.tb
WHERE Persons='g'
--注：更多替代方法参考.3.1查询示例，只需替换行集函数(OPENDATASOURCE)内的内容即可。
--4. 删除链接服务器方法
--如果以后不再使用时可删除链接信息
--4.1 删除登录信息(或叫删除链接服务器登录名映射)
EXEC sp_droplinkedsrvlogin 'LinkName',NULL
/*例如：EXEC sp_droplinkedsrvlogin 'TonyLink',NULL */
--4.2 删除链接服务器名称
EXEC sp_dropserver 'LinkName','droplogins' --如果指定droplogins，则在删除链接服务器之前要删除登录名映射
/*例如：EXEC sp_dropserver 'TonyLink','droplogins' */
--附：获取Provider Name的方法(EXEC master..xp_enum_oledb_providers)
SELECT CAST([Provider Name] AS VARCHAR(30)) ProviderName,
CAST([Provider Description] AS VARCHAR(60)) ProviderDescription
FROM OPENROWSET(
'SQLOLEDB',
'Server=.;Trusted_Connection=yes',
'SET FMTONLY OFF;
EXEC master..xp_enum_oledb_providers'
ProviderName ProviderDescription
------------------------------ ------------------------------------------------------------
SQLOLEDB Microsoft OLE DB Provider for SQL Server
DTSPackageDSO Microsoft OLE DB Provider for DTS Packages
SQLReplication.OLEDB SQL Server Replication OLE DB Provider for DTS
MSOLAP Microsoft OLE DB Provider for Analysis Services 10.0
MSDMine Microsoft OLE DB Provider For Data Mining Services
Microsoft.ACE.OLEDB.12.0 Microsoft Office 12.0 Access Database Engine OLE DB Provider
ADsDSOObject OLE DB Provider for Microsoft Directory Services
SQLNCLI10 SQL Server Native Client 10.0
MSDAIPP.DSO Microsoft OLE DB Provider for Internet Publishing
MSDASQL Microsoft OLE DB Provider for ODBC Drivers
Microsoft.Jet.OLEDB.4.0 Microsoft Jet 4.0 OLE DB Provider
MSDAOSP Microsoft OLE DB Simple Provider
MSDAORA Microsoft OLE DB Provider for Oracle
MSIDXS Microsoft OLE DB Provider for Indexing Service
(14 行受影响)
OpenRowSet相关帮助：
----------------------------------------------------------------------
包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。对于较频繁引用 OLE DB 数据源的情况，请改为使用链接服务器。有关详细信息，请参阅 链接服务器。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 访问接口的功能，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集，但 OPENROWSET 只返回第一个结果集。
OPENROWSET 还通过内置的 BULK 访问接口支持大容量操作，正是有了该访问接口，才能从文件读取数据并将数据作为行集返回。
Transact-SQL 语法约定
OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } , {
[ catalog. ] [ schema. ] object | 'query' }
| BULK 'data_file' , { FORMATFILE = 'format_file_path' [
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
[ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]
' provider_name '
字符串，表示在注册表中指定的 OLE DB 访问接口的友好名称（或 PROGID）。provider_name 没有默认值。
' datasource '
对应于特定 OLE DB 数据源的字符串常量。datasource 是要传递给访问接口的 IDBProperties 接口的 DBPROP_INIT_DATASOURCE 属性，该属性用于初始化访问接口。通常，该字符串包含数据库文件的名称、数据库服务器的名称，或者访问接口能理解的用于定位数据库的名称。
' user_id '
字符串常量，它是传递给指定 OLE DB 访问接口的用户名。user_id 为连接指定安全上下文，并作为 DBPROP_AUTH_USERID 属性传入以初始化访问接口。user_id 不能是 Microsoft Windows 登录名称。
' password '
字符串常量，它是传递给 OLE DB 访问接口的用户密码。在初始化访问接口时，password 作为 DBPROP_AUTH_PASSWORD 属性传入。password 不能是 Microsoft Windows 密码。
' provider_string '
访问接口特定的连接字符串，作为 DBPROP_INIT_PROVIDERSTRING 属性传入以初始化 OLE DB 访问接口。通常 provider_string 封装初始化访问接口所需的所有连接信息。有关 SQL Server Native Client OLE DB 访问接口可识别的关键字列表，请参阅Initialization and Authorization Properties。
指定对象所在的目录或数据库的名称。
架构的名称或指定对象的对象所有者名称。
对象名，它唯一地标识出将要操作的对象。
字符串常量，发送到访问接口并由访问接口执行。SQL Server 的本地实例不处理该查询，但处理由访问接口返回的查询结果（传递查询）。有些访问接口并不通过表名而是通过命令语言提供其表格格式数据，将传递查询用于这些访问接口是非常有用的。只要查询访问接口支持 OLE DB Command 对象及其强制接口，那么在远程服务器上就支持传递查询。有关详细信息，请参阅 SQL Server Native Client (OLE DB) Reference。
使用 OPENROWSET 的 BULK 行集访问接口读取文件中的数据。在 SQL Server 中，OPENROWSET 无需将数据文件中的数据加载到目标表，便可读取这些数据。这样便可在单个 SELECT 语句中使用 OPENROWSET。
BULK 选项的参数可对何时开始和结束数据读取、如何处理错误以及如何解释数据提供有效控制。例如，可以指定以类型为 varbinary、varchar 或 nvarchar 的单行单列行集的形式读取数据文件。默认行为详见随后的参数说明。
有关如何使用 BULK 选项的信息，请参阅本主题后面部分的“备注”。有关 BULK 选项所需权限的信息，请参阅本主题后面的“权限”部分。
当用于以完整恢复模式导入数据时，OPENROWSET (BULK ...) 不优化日志记录。
有关为大容量导入准备数据的信息，请参阅准备用于大容量导出或大容量导入的数据。
' data_file '
数据文件的完整路径，该文件的数据将被复制到目标表中。
FORMATFILE = 'format_file_path'
指定格式化文件的完整路径。SQL Server 支持两种格式化文件类型：XML 和非 XML。
格式化文件对定义结果集中的列类型是必需的。唯一的例外情况是指定 SINGLE_CLOB、SINGLE_BLOB 或 SINGLE_NCLOB 时；在这种情况下，不需要格式化文件。
有关格式化文件的信息，请参阅使用格式化文件大容量导入数据。
指定 BULK 选项的一个或多个参数。
CODEPAGE = { 'ACP '| 'OEM '| 'RAW '| 'code_page' }
指定该数据文件中数据的代码页。仅当数据含有字符值大于 127 或小于 32 的 char、varchar 或 text 列时，CODEPAGE 才是适用的。
建议在格式化文件中为每个列指定一个排序规则名称。
CODEPAGE 值
将数据类型为 char、varchar 或 text 的列由 ANSI/Microsoft Windows 代码页 (ISO 1252) 转换为 SQL Server 代码页。
OEM（默认值）
将数据类型为 char、varchar 或 text 的列由系统 OEM 代码页转换为 SQL Server 代码页。
不执行从一个代码页到另一个代码页的转换。这是执行最快的选项。
指示编码数据文件中的字符数据所在的源代码页；例如，850。该代码页对 SQL Server 数据库引擎正确解释输入数据是必需的。
ERRORFILE = 'file_name'
指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。这些行将按原样从数据文件复制到此错误文件中。
错误文件在开始执行命令时创建。如果该文件已存在，将引发一个错误。此外，还创建了一个扩展名为 .ERROR.txt 的控制文件。此文件引用错误文件中的每一行并提供错误诊断。纠正错误后即可加载数据。
FIRSTROW = first_row
指定要加载的第一行的行号。默认值为 1，指示指定数据文件的第一行。通过对行终止符进行计数来确定行号。
LASTROW = last_row
指定要加载的最后一行的行号。默认值为 0，指示指定数据文件中的最后一行。
MAXERRORS = maximum_errors
指定格式化文件中定义的、在 OPENROWSET 引发异常之前可以发生的语法错误或格式有误行的最大数目。在达到 MAXERRORS 之前，OPENROWSET 会忽略每个错误行，不加载它，并将其计为一个错误。
maximum_errors 的默认值为 10。
MAX_ERRORS 不适用于 CHECK 约束，也不适用于 money 和 bigint 数据类型的转换。
ROWS_PER_BATCH = rows_per_batch
指定数据文件中近似的数据行数量。该值应与实际行数相同。
OPENROWSET 始终以单批形式导入数据文件。但如果将 rows_per_batch 的值指定为 > 0，则查询处理器在查询计划中分配资源时将使用 rows_per_batch 的值作为提示。
默认情况下，ROWS_PER_BATCH 未知。指定 ROWS_PER_BATCH = 0 相当于忽略 ROWS_PER_BATCH。
ORDER ( { column [ ASC | DESC ] } [ ,...n ] [ UNIQUE ] )
一个用于指定数据文件中数据的排序方式的可选提示。默认情况下，大容量操作假定数据文件未排序。如果查询优化器能够利用指定顺序来生成更有效的查询计划，则性能可能会得到改善。指定一个排序可以取得益处的示例包括：
将行插入到具有***索引的表，其中行集数据按***索引键进行排序。
将行集与另一个表联接，其中排序列和联接列匹配。
通过排序列聚合行集数据。
将行集用作查询的 FROM 子句中的源表，其中排序列和联接列匹配。
UNIQUE 指定数据文件不能有重复条目。
如果数据文件中的实际行没有根据指定的顺序进行排序，或者如果指定了 UNIQUE 提示并且存在重复键，则返回错误。
使用 ORDER 时列别名是必需的。列别名列表必须引用由 BULK 子句正在访问的派生表。在 ORDER 子句中指定的列名将引用此列别名列表。不能指定大值类型（varchar(max)、nvarchar(max)、varbinary(max) 和 xml）和大型对象 (LOB) 类型（text、ntext 和 image）列。
SINGLE_BLOB
将 data_file 的内容作为类型为 varbinary(max) 的单行单列行集返回。
重要提示：
我们建议您仅使用 SINGLE_BLOB 选项（而不是 SINGLE_CLOB 和 SINGLE_NCLOB）导入 XML 数据，因为只有 SINGLE_BLOB 支持所有的 Windows 编码转换。
SINGLE_CLOB
通过以 ASCII 格式读取 data_file，使用当前数据库的排序规则将内容作为类型为 varchar(max) 的单行单列行集返回。
SINGLE_NCLOB
通过以 UNICODE 格式读取 data_file，使用当前数据库的排序规则将内容作为类型为 nvarchar(max) 的单行单列行集返回。
只有在以下情况下才能使用 OPENROWSET 访问 OLE DB 数据源中的远程数据：指定访问接口的 DisallowAdhocAccess 注册表选项已显式设置为 0，并启用了 Ad Hoc Distributed Queries 高级配置选项。如果未设置这些选项，则默认行为不允许即席访问。
访问远程 OLE DB 数据源时，服务器不会自动委托可信连接的登录标识，客户端通过此登录标识才能连接到正在查询的服务器。必须配置身份验证委托。有关详细信息，请参阅为委托配置链接服务器。
如果 OLE DB 访问接口在指定的数据源中支持多个目录和架构，那么就需要目录及架构名称。如果 OLE DB 访问接口并不支持多个目录和架构，那么可以忽略 catalog 和 schema 的值。如果访问接口只支持架构名称，那么必须指定一个格式为 schema.object 的两部分名称。如果访问接口只支持目录名称，那么必须指定一个格式为 catalog.schema.object 的三部分名称。必须为使用 SQL Server Native Client OLE DB 访问接口的传递查询指定由三部分组成的名称。有关详细信息，请参阅 Transact-SQL 语法约定 (Transact-SQL)。
OPENROWSET 不接受参数变量。
使用带有 BULK 选项的 OPENROWSET
以下 Transact-SQL 增强功能支持 OPENROWSET(BULK...)函数：
与 SELECT 一起使用的 FROM 子句可以调用 OPENROWSET(BULK...)而非表名，同时可以实现完整的 SELECT 功能。
带有 BULK 选项的 OPENROWSET 在 FROM 子句中需要有一个相关名称，也称为范围变量或别名。可以指定列别名。如果未指定列别名列表，则格式化文件必须具有列名。指定列别名会覆盖格式化文件中的列名，例如：
FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
SELECT...FROM OPENROWSET(BULK...)语句将直接查询文件中的数据，无需将数据导入表中。SELECT…FROM OPENROWSET(BULK...)语句还可以通过使用格式化文件指定列名和数据类型，从而列出大容量列别名。
通过将 OPENROWSET(BULK...)用作 INSERT 或 MERGE 语句中的源表，将数据文件中的数据大容量导入 SQL Server 表中。有关详细信息，请参阅使用 BULK INSERT 或 OPENROWSET(BULK...) 导入大容量数据。
OPENROWSET BULK 选项与 INSERT 语句一起使用时，BULK 子句支持表提示。BULK 子句除了接受 TABLOCK 等常规表提示之外，还可以接受下列专用表提示：IGNORE_CONSTRAINTS（仅忽略 CHECK 和 FOREIGN KEY 约束）、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。有关详细信息，请参阅表提示 (Transact-SQL)。
有关如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 语句的信息，请参阅导入和导出大容量数据。有关何时在事务日志中记录由大容量导入执行的行插入操作的信息，请参阅在大容量导入中按最小方式记录日志的前提条件。
使用 OPENROWSET 时，请务必了解 SQL Server 是如何处理模拟的。有关安全注意事项的信息，请参阅使用 BULK INSERT 或 OPENROWSET(BULK...) 导入大容量数据。
大容量导出或导入 SQLXML 文档
若要大容量导出或导入 SQLXML 数据，请在格式化文件中使用下列数据类型之一。
SQLCHAR 或 SQLVARYCHAR
在客户端代码页或排序规则隐含的代码页中发送数据。
SQLNCHAR 或 SQLNVARCHAR
以 Unicode 格式发送数据。
SQLBINARY 或 SQLVARYBIN
不经任何转换即发送数据。
OPENROWSET 权限由传递给 OLE DB 访问接口的用户名的权限确定。若要使用 BULK 选项，则需要有 ADMINISTER BULK OPERATIONS 权限。
A. 将 OPENROWSET 与 SELECT 和 SQL Server Native Client OLE DB 访问接口一起使用
以下示例使用 SQL Server Native Client OLE DB 访问接口访问 HumanResources.Department 表，该表位于远程服务器 Seattle1 上的 AdventureWorks 数据库中。（使用 SQLNCLI 并且 SQL Server 将重定向到 SQL Server Native Client OLE DB 访问接口的最新版本。）使用 SELECT 语句定义返回的行集。访问接口字符串包含 Server 和 Trusted_Connection 关键字。这些关键字由 SQL Server Native Client OLE DB 访问接口识别。
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name') AS
B. 使用 Microsoft OLE DB Provider for Jet
以下示例通过 Microsoft OLE DB Provider for Jet 访问 Microsoft Access Northwind 数据库中的 Customers 表。
该示例假定已经安装了 Access。若要运行该示例，则必须安装 Northwind 数据库。有关如何安装 Northwind 数据库的详细信息，请参阅下载 Northwind 和 pubs 示例数据库。
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
C. 使用 OPENROWSET 和 INNER JOIN 中的另一个表
以下示例从 SQL Server Northwind 数据库的本地实例中的 Customers 表以及存储在同一计算机上的 Access Northwind 数据库中的 Orders 表选择所有数据。
该示例假定已经安装了 Access。若要运行该示例，则必须安装 Northwind 数据库。有关如何安装 Northwind 数据库的详细信息，请参阅下载 Northwind 和 pubs 示例数据库。
USE Northwind
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
ON c.CustomerID = o.CustomerID
D. 使用 OPENROWSET 将文件数据大容量插入 varbinary(max) 列中
以下示例创建一个用于演示的小型表，并将名为 Text1.txt 的文件（位于 C: 根目录）中的文件数据插入 varbinary(max) 列中。
USE AdventureWorks
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max))
INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
E. 将 OPENROWSET BULK 访问接口用于格式化文件以检索文本文件中的行
以下示例使用格式化文件检索用制表符分隔的文本文件 values.txt 中的行，该文件包含下列数据：
Data Item 1
Data Item 2
Data Item 3
格式化文件 values.fmt 说明 values.txt 中的列：
SQL_Latin1_General_Cp437_BIN
Description
SQL_Latin1_General_Cp437_BIN
下面的语句是检索此数据的查询：
SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS
--------------------------------------------------------------------
OpenDataSource相关帮助：
--------------------------------------------------------------------
不使用链接服务器的名称，而提供特殊的连接信息，并将其作为四部分对象名的一部分。
OPENDATASOURCE ( provider_name, init_string )
provider_name
注册为用于访问数据源的 OLE DB 访问接口的 PROGID 的名称。provider_name 的数据类型为 char，无默认值。
init_string
连接字符串，该字符串将要传递给目标提供程序的 IDataInitialize 接口。提供程序字符串语法是以关键字值对为基础的，这些关键字值对由分号隔开，例如：“keyword1=value; keyword2=value”。
若要了解提供程序上支持的特定关键字值对，请参阅 Microsoft Data Access SDK。该文档定义了基本语法。下表列出了 init_string 参数中最常用的关键字。
OLE DB 属性
有效值和说明
DBPROP_INIT_DATASOURCE
要连接的数据源的名称。不同的提供程序用不同的方法对此进行解释。对于 SQL Server Native Client OLE DB 访问接口，这指示服务器的名称。对于 Jet OLE DB 访问接口来说，这指示 .mdb 文件或 .xls 文件的完整路径。
DBPROP_INIT_LOCATION
要连接的数据库的位置。
DBPROP_INIT_PROVIDERSTRING
提供程序特定的连接字符串。
DBPROP_INIT_TIMEOUT
达到该超时值后，连接尝试将失败。
DBPROP_AUTH_USERID
用于该连接的用户 ID。
DBPROP_AUTH_PASSWORD
用于该连接的密码。
DBPROP_INIT_CATALOG
连接到数据源时的初始或默认的目录名称。
集成安全性
DBPROP_AUTH_INTEGRATED
SSPI，指定 Windows 身份验证
仅当 DisallowAdhocAccess 注册表选项针对指定的提供程序显式设置为 0，并且启用 Ad Hoc Distributed Queries 高级配置选项时，OPENDATASOURCE 才可用于访问 OLE DB 数据源中的远程数据。如果未设置这些选项，则默认行为不允许即席访问。
OPENDATASOURCE 函数可以在能够使用链接服务器名的相同 Transact-SQL 语法位置中使用。因此，可以将 OPENDATASOURCE 用作四部分名称的第一部分，该部分名称引用 SELECT、INSERT、UPDATE 或 DELETE 语句中的表或视图的名称；或者引用 EXECUTE 语句中的远程存储过程。当执行远程存储过程时，OPENDATASOURCE 应该引用 SQL Server 的另一个实例。OPENDATASOURCE 不接受参数变量。
与 OPENROWSET 函数类似，OPENDATASOURCE 应该只引用那些不经常访问的 OLE DB 数据源。对于访问次数较频繁的任何数据源，请为它们定义链接服务器。无论 OPENDATASOURCE 还是 OPENROWSET 都不能提供链接服务器定义的全部功能，例如，安全管理以及查询目录信息的功能。每次调用 OPENDATASOURCE 时，都必须提供所有的连接信息（包括密码）。
重要提示：
Windows 身份验证比 SQL Server 身份验证要安全得多。应尽量使用 Windows 身份验证。OPENDATASOURCE 不应该用于连接字符串中的显式密码。
任何用户都可以执行 OPENDATASOURCE。用于连接到远程服务器的权限由连接字符串确定。
以下示例将创建与服务器 London 上的 SQL Server 实例 Payroll 的即席连接，并查询 AdventureWorks.HumanResources.Employee 表。（使用 SQLNCLI 并且 SQL Server 将重定向到 SQL Server Native Client OLE DB 访问接口的最新版本。）
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=London\PIntegrated Security=SSPI')
.AdventureWorks.HumanResources.Employee
-------------------------------------------------------------------
OpenQuery相关帮助：
-------------------------------------------------------------------
对给定的链接服务器执行指定的传递查询。该服务器是 OLE DB 数据源。OPENQUERY 可以在查询的 FROM 子句中引用，就好象它是一个表名。OPENQUERY 也可以作为 INSERT、UPDATE 或 DELETE 语句的目标表进行引用。但这要取决于 OLE DB 访问接口的功能。尽管查询可能返回多个结果集，但是 OPENQUERY 只返回第一个。
OPENQUERY ( linked_server ,'query' )
linked_server
表示链接服务器名称的标识符。
在链接服务器中执行的查询字符串。该字符串的最大长度为 8 KB。
OPENQUERY 不接受其参数的变量。
在 SQL Server 2000 和更高版本中，OPENQUERY 不能用于对链接服务器执行扩展存储过程。但是，通过使用四部分名称，可以在链接服务器上执行扩展存储过程。例如：
EXEC SeattleSales.master.dbo.xp_msver
任何用户都可以执行 OPENQUERY。用于连接到远程服务器的权限是从为链接服务器定义的设置中获取的。
A. 执行 SELECT 传递查询
以下示例将使用“用于 Oracle 的 Microsoft 访问接口”针对 Oracle 数据库创建一个名为 OracleSvr 的链接服务器。然后，该示例针对此链接服务器使用 SELECT 传递查询。
本示例假定已经创建了一个名为 ORCLDB 的 Oracle 数据库别名。
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
B. 执行 UPDATE 传递查询
以下示例针对示例 A 中创建的链接服务器使用 UPDATE 传递查询。
UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')
SET name = 'ADifferentName';
C. 执行 INSERT 传递查询
以下示例针对示例 A 中创建的链接服务器使用 INSERT 传递查询。
INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');
D. 执行 DELETE 传递查询
以下示例使用 DELETE 传递查询删除示例 C 中插入的行。
DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');
-----------------------------------------------------------------------
from:http://blog.csdn.net/htl258/archive//5695391.aspx
阅读(6265) | 评论(0) | 转发(1) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。}