[SQL server]跨服务器链接数据库操作

前段时间在写一个系统的时候要用到了跨网段跨机子读数据．

如果这是在程序里写，那很简单，easy多了．但要是在Sql server里操作.......呵，有点点烦．

找了公司个高手，他发了个存储过程过来．试试，嘿嘿，还真D可以．

贴上来做个记录吧：

 

/*--------------------

-- 运行示例：Pro_LinkServer 'dpserver','192.168.1.1','sa','sapassword'
--运行后操作数据库：select * from dpserver.sun1000.dbo.IC_USER

---------------------*/

CREATE procedure Pro_LinkServer
@linknm sysname,
@rmserv sysname,
@rmuser sysname,
@rmpawd sysname,
@flag tinyint=0 --0:add 1:drop 2:list
as
begin
if @flag!=2 and isnull(@linknm,'')=''
begin
raiserror('链接服务器名不能为空。',16,-1)
return
end

if @flag=0 and isnull(@rmuser,'')=''
begin
raiserror('远程登陆用户名不能为空。',16,-1)
return
end

declare @err int

if @flag=0
begin
if isnull(@rmserv,'')='' select @rmserv='127.0.0.1'
exec @err=master.dbo.sp_addlinkedserver @linknm

if @err=0
begin
exec @err=master.dbo.sp_serveroption @linknm,'use remote collation','false'
if isnull(@rmserv,'') not in ('',@linknm)
exec @err=master.dbo.sp_setnetname @linknm,@rmserv
if @err=0
exec @err=master.dbo.sp_addlinkedsrvlogin @linknm,'false',null,@rmuser,@rmpawd
end
end else
if @flag=1
begin
exec @err=master.dbo.sp_dropserver @linknm,'droplogins'
end else
if @flag=2
begin
exec @err=master.dbo.sp_helpserver
select * from master.dbo.sysservers
end

if @err=0 print 'success' else print 'fail'

end
GO

--------------------------------------------------其它摸索时的资料

EXEC    sp_addlinkedserver    @server='XYY-FTP', @srvproduct='',
                                @provider='SQLOLEDB'

sp_dropserver 'XYY-FTP'

 

USE master
GO
EXEC  sp_addlinkedserver   
     'XYY-FTP',   
     '',   
     'SQLOLEDB', 
     NULL, 
     NULL, 
     'Provider=sqloledb; User ID=sa; Password=sa2zweb; Initial Catalog=db_bester; Data Source=192.168.1.2' 
GO 

 

select * from [XYY-FTP].[DB_Bester].[DBuser_Bester].[T_Vote]

 

sp_helpserver

sp_dropserver 'LONDON Payroll'

EXEC sp_addlinkedserver
   'LONDON Payroll',
   '',
   'SQLOLEDB',
   NULL,
   NULL,
   'provider={SQL Server};SERVER=192.168.1.2;UID=sa;PWD=sa2zweb;'
GO

sp_dropserver 'LONDON'
USE master
GO
EXEC sp_addlinkedserver
    'LONDON',
    N'SQL Server'
GO

select top 10 * from [kkkk].[DB_Orders].[DBuser_WebOrder].[TN_TryOrder]

 

sp_helpserver

set ANSI_NULLS On
set ANSI_WARNINGS on

EXEC sp_droplinkedsrvlogin 'kkkk',null

if exists(select 1 from master..sysservers where srvname='kkkk')
exec sp_dropserver 'kkkk','droplogins'
go

exec sp_addlinkedserver 'kkkk','','SQLOLEDB','192.168.1.2'
exec sp_addlinkedsrvlogin 'kkkk','false',Null,'sa','sa2zweb'
go

select distinct Fld_OrderProductName from  [kkkk].DB_Orders.DBuser_WebOrder.T_Order

sp_dropserver 'RRR'

---------------------------------------------
exec sp_addlinkedserver 'RRR'
EXEC sp_setnetname 'RRR','192.168.1.2'
exec sp_addlinkedsrvlogin 'RRR','false',Null,'sa','sa2zweb'
Pro_LinkServer 'dpserver','192.168.1.2','sa','sa2zweb'

select distinct Fld_OrderProductName from  [RRR].DB_Orders.DBuser_WebOrder.T_Order