前段时间在写一个系统的时候要用到了跨网段跨机子读数据.
如果这是在程序里写,那很简单,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