sql访问远程的数据库
1、访问sql:
1)、select * from opendatasource('SQLOLEDB','data source=hero;user id=sa').xyjw.dbo.dic_depart
2)、select * from openrowset('SQLOLEDB','hero';'sa';'','select * from xyjw.dbo.dic_folk') as a
3)、先添加链接服务器
exec sp_addlinkedserver @server='hero',@provider='sqloledb',@srvproduct='',@datasrc='远程服务器名'
exec sp_addlinkedsrvlogin @rmtsrvname='hero',@useself='false',@locallogin='',@rmtuser='sa',@rmtpassword='密码'
select * from hero.xyjw.dbo.dic_folk
1、访问sql:
1)、select * from opendatasource('SQLOLEDB','data source=hero;user id=sa').xyjw.dbo.dic_depart
2)、select * from openrowset('SQLOLEDB','hero';'sa';'','select * from xyjw.dbo.dic_folk') as a
3)、先添加链接服务器
exec sp_addlinkedserver @server='hero',@provider='sqloledb',@srvproduct='',@datasrc='远程服务器名'
exec sp_addlinkedsrvlogin @rmtsrvname='hero',@useself='false',@locallogin='',@rmtuser='sa',@rmtpassword='密码'
select * from hero.xyjw.dbo.dic_folk
在A上可以运行以下语句
SELECT *FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=135.125.33.139;User ID=sa;Password=123').WY_CNO_CMP_DB.DBO.BSS_BCKPOWER
在B上则不能运行一下语句
SELECT *FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=135.125.33.137;User ID=sa;Password=123').WY_CNO_CMP_DB.DBO.BSS_BCKPOWER表均存在,上周还是可以运行的,前几天不知道修改了什么才造成的,现在就是恢复不回去,超级郁闷
http://blog.csdn.net/itneste/archive/2009/04/27/4128647.aspx