跨数据库取数据(sqlserver2000)

一条语句执行跨越若干个数据库
我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?
第一种方法:
select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名

第二种方法:
先使用联结服务器:


EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
exec sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
GO

然后你就可以如下：


select * from 别名.库名.dbo.表名
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
go


下面介绍第一种方法例子
CREATE VIEW V_ReadingDoNotice
as
select id,title,content,author,t2.userid,pdate,accessory,'MMIS' as systemName,'CTCMIS/bbs/plashow.jsp?id='+cast(id as varchar(20)) as linkurl from
OPENDATASOURCE('SQLOLEDB','Data Source=连接IP;User ID=sa;Password=密码').gzdevmmis.dbo.S_placard t1
left join OPENDATASOURCE('SQLOLEDB','Data Source=连接IP;User ID=sa;Password=密码').gzdevmmis.dbo.S_User t2
on t1.author = t2.uname
union all
select id,title,content,author,t1.userid,pdate,accessory,'CPMIS' as systemName,'CTCMIS/bbs/plashow.jsp?id='+cast(id as varchar(20)) as linkurl
from OPENDATASOURCE('SQLOLEDB','Data Source=连接IP;User ID=sa;Password=密码').gzcdx.dbo.S_placard t2
left join OPENDATASOURCE('SQLOLEDB','Data Source=连接IP;User ID=sa;Password=密码').gzcdx.dbo.S_User t1
on t2.author = t1.uname