Hello,

The below is the code.I am getting the above error while executing this.

Just What i want is if the error comes then it will go to the next server and repeat the execution..

Can any one help me and

create PROCEDURE SP_Link 

AS 

DECLARE @IP VARCHAR(100)   

DECLARE @Password VARCHAR(100)   

DECLARE @DBName VARCHAR(100)   

DECLARE @SQL_Password VARCHAR(100)   

DECLARE @OSQL VARCHAR(100)   

BEGIN 

 DECLARE CUR_FETCH1 CURSOR FOR SELECT SERVER_IP,SQL_PASSWORD,DB_NAME FROM RemoteInfo 

 OPEN CUR_FETCH1 

 FETCH NEXT FROM CUR_FETCH1 INTO @IP,@SQL_Password,@DBName 

 WHILE @@FETCH_STATUS=0 

 BEGIN 

  SET @OSQL='osql -S '+@IP +' -U sa -P '+@SQL_Password +' -d master -Q"SELECT Name FROM sysdatabases"'  

  IF EXISTS (SELECT * FROM SYSOBJECTS WHERE TYPE='U' AND NAME='DBases')   

  BEGIN 

   drop table DBases         

  END  --2   

  CREATE TABLE DBases    

  (    

      dbname SYSNAME NULL    

   );    

  INSERT  into DBases   

  EXEC master..xp_cmdshell @OSQL   

  IF EXISTS(SELECT 1 FROM DBases WHERE LTRIM(RTRIM(DbName))=N'master') 

  BEGIN 

   DECLARE @DROPSERVER VARCHAR(50) 

   DECLARE @DQUERY VARCHAR(800) 

   DECLARE @CNT INT 

   SET @DROPSERVER=('SP_DROPSERVER ''L_REMOTE'',''DROPLOGINS''') 

   IF EXISTS(SELECT * FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME LIKE 'L_REMOTE') 

   EXEC(@DROPSERVER) 

   EXEC SP_ADDLINKEDSERVER 

   @SERVER='L_REMOTE', 

   @SRVPRODUCT='', 

   @PROVIDER='SQLOLEDB', 

   @CATALOG=@DBname, 

   @DATASRC=@IP 

   EXEC SP_ADDLINKEDSRVLOGIN 

   @RMTSRVNAME='L_REMOTE', 

   @USESELF='False', 

   @RMTUSER='sa', 

   @RMTPASSWORD=@SQL_Password 

   SET @DQUERY='' 

   IF @DBName LIKE '%crm%' 

   BEGIN 

    SET @DQUERY='SELECT MAX(RO)AS DATE FROM SERVICE WHERE RO BETWEEN GETDATE()-5 AND GETDATE()' 

   END 

   ELSE 

   BEGIN 

    SET @DQUERY='SELECT MAX(DEAL)AS DATE FROM PURCHASE WHERE DEAL BETWEEN GETDATE()-5 AND GETDATE()' 

   END 

       IF EXISTS (SELECT * FROM TempDB..SYSOBJECTS WHERE TYPE='U' AND NAME='temp_data') 

                    BEGIN 

                        DROP TABLE TempDB..temp_data

      end

   SET @DQUERY='SELECT * INTO TEMPDB.DBO.temp_data FROM OPENQUERY(L_REMOTE,'''+@DQUERY+''')' 

   EXEC(@DQUERY) 

   SELECT * FROM TEMPDB.DBO.temp_data  

   SELECT @CNT=COUNT(*) FROM TEMPDB.DBO.temp_data  

   IF @CNT>=1 

   BEGIN 

    DECLARE @D_DATE VARCHAR(100) 

    DECLARE CUR_TEMPDATA CURSOR FOR SELECT * FROM TEMPDB.DBO.temp_data  

    OPEN CUR_TEMPDATA 

    FETCH CUR_TEMPDATA INTO @D_DATE 

    WHILE @@FETCH_STATUS=0 

    BEGIN 

     INSERT INTO Final_Data SELECT @D_DATE,@DBname 

     FETCH CUR_TEMPDATA INTO @D_DATE  

    END 

   END 

   CLOSE CUR_TEMPDATA 

   DEALLOCATE CUR_TEMPDATA 

  END  

  ELSE 

  BEGIN 

   PRINT 'No Connection To The Remote Server' 

  END 

  FETCH NEXT FROM CUR_FETCH1 INTO @IP,@SQL_Password,@DBName 

 END 

END 

CLOSE CUR_FETCH1 

DEALLOCATE CUR_FETCH1