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

('','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 >= '''''

                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