sqlserver跨服务器连接数据库
文章目录
开启组件语法添加链接关闭组件开启组件
首先开启Ad Hoc Distributed Queries组件,在sql查询编辑器中执行如下语句:
exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure
开启组件的原因语法中会说到。
语法
openrowset()方法是连接其他服务器数据库的命令。
语法:openrowset(‘SQLOLEDB’,‘ip,端口’;‘username’;‘password’,‘sql语句或者表’)。
两个示例:
1.select * from openrowset('SQLOLEDB','127.0.0.1,8080';'sa';'12345678',[Database].[dbo].table) ; // 直接连接表可以不用带引号
2.select * from openrowset('SQLOLEDB','127.0.0.1,8080';'sa';'12345678','select id , name from [Database].[dbo].table') ;//如果是sql语句需要带引号
注意:如果不开启组件,会报下面这个错误。
添加链接
如果感觉每次都是用openrowset()方法太麻烦了,可以添加一个链接服务器。
EXEC sp_addlinkedserver@server='iptest',--服务器别名(可以用ip,也可以自己设置,只是个名称)@srvproduct=' ', --链接服务器的OLEDB数据源的产品名称,对象是sqlserver可以不指定@provider='SQLOLEDB', --提供程序与数据源交互的动态链接库,是个连接对象,无需更改@datasrc='101.1.101.101' --被访问的服务器的ip
添加用户登录链接。
EXEC sp_addlinkedsrvlogin 'iptest', --与上面的服务器别名保持一致'false', -- 不用管NULL, --不用管'administrator', --被访问的服务器帐号 'password' --被访问的服务器密码
此时执行:
select * from [iptest].[Database].[dbo].[table]
和
select * from openrowset(‘SQLOLEDB’,‘127.0.0.1,8080’;‘sa’;‘12345678’,
[Database].[dbo].table) ;
效果是一样的。
建议:如果确定数据库名和表名没有重复的或者以关键字命名的,中括号可以去掉。
关闭组件
注意:如果不需要链接需要关闭组件,因为开启会有安全隐患,避免别人攻击。
exec sp_configure 'Ad Hoc Distributed Queries',0reconfigureexec sp_configure 'show advanced options',0reconfigure
如果链接不再使用,可以删除已经存在的某个链接。
Exec sp_droplinkedsrvlogin 服务器别名,NullExec sp_dropserver 服务器别名