SQL Server 连接服务器 link server 存储过程
SQL Server 连接服务器 link server 存储过程
有时候我们需要查询远程服务器的数据库，查询的方式 [servername].[dbname].[owner].[object]
比如 Select * from [172.10.10.2].[test].dbo.table
但是在没有建立link server之前是不可以查的,这里我写了一个通用的存储过程来建立link server的。
代码如下：
Create procedure [dbo].[usp_PUB_Addlinkserver]
(
@ServerIP varchar(30),
@UserID varchar(50),
@Password varchar(50)
)
AS
Declare @Testlinkserver varchar(500)
IF Exists(select * from master.dbo.sysservers where srvname=@ServerIP)
BEGIN
Select 'Failed' as iResult,'Link Server already exist!' as iDescription,@ServerIP as ServerIP
--Exec sp_dropserver @ServerIP,'droplogins'
return
END
Begin Try
Exec sp_addlinkedserver @ServerIP,'','SQLOLEDB',@ServerIP
Exec sp_addlinkedsrvlogin @ServerIP,'false',null,@UserID,@Password
set @Testlinkserver='Select top 1 *into #tmp from ['+@ServerIP+'].master.dbo.sysservers '
Exec(@Testlinkserver)
Insert into __Temp
Select 'Succeed' as iResult,'Operation Completed!' as iDescription,@ServerIP as ServerIP
return
END Try
BEGIN Catch
Insert into __Temp
Select 'Failed' as iResult,'UID password incorrect!' as iDescription,@ServerIP as ServerIP
Exec sp_dropserver @ServerIP,'droplogins'
END Catch
……