SQL Server 阻止了对组件 /'Ad Hoc Distributed Queries/' 的访问
想用openrowset查询远程数据库,结果提示SQL Server 阻止了对组件 /'Ad Hoc Distributed Queries/' 的访问错误,在网上搜索了一下,找到解决方法:
启用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
SELECT * FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=ServerName;User ID=sa;Password=sa' ).DataBaseName.dbo.Table
select * from openrowset( 'SQLOLEDB ', 'IP地址'; '用户名'; '密码',数据库名.dbo.表名)
(源文出处:http://hi.baidu.com/bb3852/blog/item/908497af9d8ea8fffaed5068.html)
|