[sql异常]SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的

原创

水狼一族 2022-03-21 11:10:24 博主文章分类：sql ©著作权

©著作权归作者所有：来自51CTO博客作者水狼一族的原创作品，请联系作者获取转载授权，否则将追究法律责任

//执行远程数据库表查询
select *  from openrowset( 'SQLOLEDB', '192.168.5.547'; 'sa'; '密码',[数据库名称].[dbo].[表名])

出现异常：

消息 15281，级别 16，状态 1，第 1 行

SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

利用沙盒模式失败时需要用到，解决方法：

//启用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

sp_addlinkedserver 
创建一个链接的服务器，使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问。在使用 sp_addlinkedserver 创建链接的服务器之后，此服务器就可以执行分布式查询。如果链接服务器定义为 Microsoft® SQL Server™，则可执行远程存储过程。 

语法 
sp_addlinkedserver [ @server = ] 'server' 
[ , [ @srvproduct = ] 'product_name' ] 
[ , [ @provider = ] 'provider_name' ] 
[ , [ @datasrc = ] 'data_source' ] 
[ , [ @location = ] 'location' ] 
[ , [ @provstr = ] 'provider_string' ] 
[ , [ @catalog = ] 'catalog' ] 
语法 
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
[ , [ @useself = ] 'useself' ] 
[ , [ @locallogin = ] 'locallogin' ] 
[ , [ @rmtuser = ] 'rmtuser' ] 
[ , [ @rmtpassword = ] 'rmtpassword' ] 


[@useself =] 'useself' 

决定用于连接到远程服务器的登录名称。useself 的数据类型为 varchar(8)，默认设置为 TRUE。true 值指定 SQL Server 验证的登录使用其自己的凭据以连接到 rmtsrvname，而忽略 rmtuser 和 rmtpassword 参数。false 值指定 rmtuser 和 rmtpassword 参数用来连接到特定 locallogin 的 rmtsrvname。如果 rmtuser 和 rmtpassword 也设置为 NULL，则连接链接服务器不需使用任何登录或密码。对于 Windows NT 身份验证登录而言，useself 为 true 则无效，除非 Microsoft Windows NT&reg; 环境支持安全帐户委托，并且，提供程序支持 Windows 身份验证（此时不再需要创建值为 true 的映射，不过创建仍然有效）。 

[ @locallogin =] 'locallogin' 

本地服务器上的登录。locallogin 的数据类型为 sysname，默认设置为 NULL。NULL 值指定此条目将应用于所有连接到 rmtsrvname 的本地登录。如果值不为 NULL，则 locallogin 可以是一个 SQL Server 登录或 Windows NT 用户。必须授予 Windows NT 用户直接访问 SQL Server 或通过其作为已授予访问权限的组的成员来访问 SQL Server 的权限。   
不同服务器数据库之间的数据操作
--创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' 

--查询示例 
select * from ITSV.数据库名.dbo.表名 

--导入示例 
select * into 表 from ITSV.数据库名.dbo.表名 

--以后不再使用时删除链接服务器 
exec sp_dropserver 'ITSV ', 'droplogins ' 

--连接远程/局域网数据(openrowset/openquery/opendatasource) 
--1、openrowset 

--查询示例 
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 

--生成本地表 
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 

--把本地表导入远程表 
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
select *from 本地表 

--更新本地表 
update b 
set b.列A=a.列A 
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b 
on a.column1=b.column1 

--openquery用法需要创建一个连接 

--首先创建一个连接创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
--查询 
select * 
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') 
--把本地表导入远程表 
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') 
select * from 本地表 
--更新本地表 
update b 
set b.列B=a.列B 
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a 
inner join 本地表 b on a.列A=b.列A 

--3、opendatasource/openrowset 
SELECT   * 
FROM   opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta 
--把本地表导入远程表 
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 
select * from