---临时访问 SELECT * FROM OPENROWSET('SQLOLEDB', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名) SELECT * FROM OPENROWSET('SQLOLEDB','192.168.0.196';'sa';'123456',tyblerp3333.dbo.zd_user) 1.开启Ad Hoc Distributed Queries组件,在sql查询编辑器中执行如下语句:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.0.11;User ID=sa;Password=123456').tyblerp3.dbo.zd_user
2.关闭Ad Hoc Distributed Queries组件,在sql查询编辑器中执行如下语句:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
--创建链接服务器
EXEC sp_addlinkedserver 'srv_lnk','','SQLOLEDB','192.168.0.11'
EXEC sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','123456'
GO
--查询示例:
SELECT * FROM srv_lnk.数据库名.dbo.表名
--导入示例:
SELECT * from srv_lnk.tyblerp3333.dbo.zd_user
GO
--以后不再使用时删除链接服务器
EXEC sp_dropserver 'srv_lnk','droplogins'