IP填你2000库的IPOPENDATASOURCE ('SQLOLEDB','DataSource=102.56.5.1;User ID=sa;Password=123').master.dbo.[table1] select * from table1
你的有下面的提示: incorrect syntax near the keyword 'OPENDATASOURCE '
直接 SELECT * FROM TABLE 这种访问。
如果是连接另外一个数据库,如同 #2的 应该是这样 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
执行之后有下面的提示: 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.
执行之后有下面的提示: 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. 呃,有开启吗 我上面说了
执行之后有下面的提示: 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. 我是这样输入的: 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_valuesexec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure exec sp_configure 'show advanced options',0 reconfigure 然后就有那个错误了。
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. 我是这样输入的: 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_valuesexec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure exec sp_configure 'show advanced options',0 reconfigure 然后就有那个错误了。
select * from table1
incorrect syntax near the keyword 'OPENDATASOURCE '
应该是这样
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
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.
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. 呃,有开启吗
我上面说了
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.
我是这样输入的:
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_valuesexec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
然后就有那个错误了。
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.
我是这样输入的:
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_valuesexec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
然后就有那个错误了。