跨服务器查询数据

use master;
go
drop database mydata;
go
create database mydata;
go
use mydata;
go
create table t_user
(
	userguid uniqueidentifier not null default newid(),
	username nvarchar(max) not null default N'',
	passwd nvarchar(max) not null default N'',
	gender nvarchar(max) not null default N'm',
	birthday smalldatetime not null default '1900-1-1',
	homeaddress nvarchar(max) not null default N'',
	idnumber nvarchar(max) not null default N'',
	primary key(userguid)
);
insert into t_user values (newid(), 'aaaa','bbbb','男','1984-1-1' ,'beijing', '111111111111111');
declare @i1 int;
set @i1 = 0;
while @i1 < 10
begin
	insert into t_user (username, passwd, gender,birthday,homeaddress,idnumber) select username,passwd,gender,birthday,homeaddress,idnumber from t_user
    set @i1 = @i1 + 1
end
select ROW_NUMBER() over (order by userguid desc)rid,* from t_user t where rid between 1 and 10





exec sp_addlinkedserver 'myserver1', '', 'sqloledb','127.0.0.1' 
go
select * from (select row_number() over (order by userguid desc) rid ,	* from 	(select * from myserver1.mydata.dbo.t_user  union all select * from myserver1.mydata.dbo.t_user)t)t2 where rid between 1 and 100
go
exec sp_dropserver 'myserver1', 'droplogins'
go


--exec sp_addlinkedserver 'myserver1', '', 'sqloledb','127.0.0.1' 
--go
--exec sp_addlinkedsrvlogin 'myserver1', 'false', null, 'sa', 'Abcdefg1'
--go
--select * from myserver1.mydata.dbo.t_user_1
--go
--exec sp_droplinkedsrvlogin  'myserver1', 'sa'
--go
--exec sp_dropserver 'myserver1', 'droplogins'
--go