34,575
社区成员
发帖
与我相关
我的任务
分享
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
GO
select * from OPENDATASOURCE ('SQLOLEDB','DataSource=192.168.30.196;User ID=sa;Password=').master.dbo.spt_values
GO
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
要用GO 隔开
执行之后有下面的提示:
Msg 15281, Level 16, State 1, Line 8
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.
[/quote]
呃,有开启吗
我上面说了[/quote]
我是这样输入的:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
select * from OPENDATASOURCE ('SQLOLEDB','DataSource=192.168.30.196;User ID=sa;Password=').master.dbo.spt_values
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
然后就有那个错误了。[/quote]Msg 15281, Level 16, State 1, Line 8
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.
[/quote]
呃,有开启吗
我上面说了[/quote]
我是这样输入的:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
select * from OPENDATASOURCE ('SQLOLEDB','DataSource=192.168.30.196;User ID=sa;Password=').master.dbo.spt_values
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
然后就有那个错误了。Msg 15281, Level 16, State 1, Line 8
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.
[/quote]
呃,有开启吗
我上面说了Msg 15281, Level 16, State 1, Line 8
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.
select * from OPENDATASOURCE ('SQLOLEDB','DataSource=*.*.*.*;User ID=username;Password=password').master.dbo.[table1]
不过,在使用openrowset/opendatasource前搜先要启用Ad Hoc Distributed Queries服务,因为这个服务不安全所以SqlServer默认是关闭的
启用Ad Hoc Distributed Queries的方法
执行下面的查询语句就可以了:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
使用完毕后,记得一定要要关闭它,因为这是一个安全隐患,切记执行下面的SQL语句
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
如果想详细的了解,参考以下
http://www.cnblogs.com/OpenCoder/archive/2010/03/18/1689321.html
OPENDATASOURCE ('SQLOLEDB','DataSource=102.56.5.1;User ID=sa;Password=123').master.dbo.[table1]
select * from table1