利用SQL 建立和删除 LINKED SERVER

USE IS21_xxx;


if object_id(‘tempdb..#tmp‘) is not null drop table #tmp

create table #tmp (
cntr nvarchar(10),
ip nvarchar(50)
)


insert into #tmp
VALUES
(‘1049‘,‘91.50.73.898‘)



DECLARE @ip nvarchar(50),
        @counter nvarchar(20),
        @Statement NVARCHAR(300),
        @sttime  datetime;
        
DECLARE CounterIp CURSOR FOR
    SELECT CNTR,IP FROM #TMP 
    
    OPEN CounterIp
    
    fetch next from CounterIp into @counter,@ip
    
    while @@fetch_status = 0
        begin
            print @ip
            --create link server
            IF NOT EXISTS(SELECT * FROM sys.servers WHERE name = @ip)
                BEGIN
                    EXEC master.dbo.sp_addlinkedserver @server = @ip, @srvproduct=N‘SQL Server‘
                    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname= @ip,@useself=N‘False‘,@locallogin=NULL,@rmtuser=N‘XX‘,@rmtpassword=‘XXXX‘
                END
  
                set @Statement = ‘select sum(sqty) as mbp into samuel_bp_2014 from [‘ + @ip + ‘].[710db_‘+ @counter +‘].dbo.xvtlg with (nolock) where pron in (‘‘k‘‘) and void = ‘‘N‘‘ and txdt >= ‘‘20140101‘‘‘

                set @sttime=getdate()
                BEGIN try
                    EXEC sp_executesql @Statement
                    EXEC sp_executesql @Statement2
                END try
                BEGIN CATCH
                    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage
                END CATCH
                PRINT ‘TimeTaken=‘ + RTRIM(((CAST(DATEDIFF(MS, @sttime, GETDATE()) AS CHAR(10))% (1000*60*60)) % (1000*60)) / 1000)     
                
                
            --drop link server
            IF EXISTS(SELECT * FROM sys.servers WHERE name = @ip)
                BEGIN
                    EXEC master.sys.sp_dropserver @ip,‘droplogins‘
                END
                
            fetch next from CounterIp into @counter,@ip
            
        end
    close CounterIp
    deallocate CounterIp