CREATE
proc
GetDataFromCenter
-- 中心服务器名
@ServerCenter sysname,
@ServerUser sysname,
@ServerPwd sysname,
-- 数据库名
@DatabaseCenter sysname,
-- 该读取数据的机器在数据分配表中的机器号
@MaID int
as
if not exists ( select srvname from master.dbo.sysservers where srvname = @ServerCenter )
-- 添加数据中心作为链接服务器
begin
exec sp_addlinkedserver @ServerCenter
exec sp_addlinkedsrvlogin @ServerCenter , ' false ' , null , @ServerUser , @ServerPwd
end
set nocount on
-- 如果post表存在将其删除
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Post] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Post ]
-- 从中心服务器读取数据到本地表post
exec ( ' select a.keywordid,a.docid,a.PositionFancy,a.CategoryFancy,a.wordpositions,a.score into dbo.post
from ' + @ServerCenter + ' . ' + @DatabaseCenter + ' .dbo.invertedindex a, ' + @ServerCenter + ' . ' + @DatabaseCenter + ' .dbo.TermPartition b
where a.keywordid=b.keywordid and b.MachineID= ' + @MaID + ' order by b.keywordid ' )
-- 建立索引
create index keywordid_idx on post(keywordid)
set nocount off
GO
-- 中心服务器名
@ServerCenter sysname,
@ServerUser sysname,
@ServerPwd sysname,
-- 数据库名
@DatabaseCenter sysname,
-- 该读取数据的机器在数据分配表中的机器号
@MaID int
as
if not exists ( select srvname from master.dbo.sysservers where srvname = @ServerCenter )
-- 添加数据中心作为链接服务器
begin
exec sp_addlinkedserver @ServerCenter
exec sp_addlinkedsrvlogin @ServerCenter , ' false ' , null , @ServerUser , @ServerPwd
end
set nocount on
-- 如果post表存在将其删除
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Post] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Post ]
-- 从中心服务器读取数据到本地表post
exec ( ' select a.keywordid,a.docid,a.PositionFancy,a.CategoryFancy,a.wordpositions,a.score into dbo.post
from ' + @ServerCenter + ' . ' + @DatabaseCenter + ' .dbo.invertedindex a, ' + @ServerCenter + ' . ' + @DatabaseCenter + ' .dbo.TermPartition b
where a.keywordid=b.keywordid and b.MachineID= ' + @MaID + ' order by b.keywordid ' )
-- 建立索引
create index keywordid_idx on post(keywordid)
set nocount off
GO
从远程sql服务器上读取数据,首先要添加连接服务器,然后才可以读取