LinkedServer loop error -- ignore & continue?

• Dird

  SSCertifiable

  Points: 5081

  More actions

  July 27, 2013 at 6:38 am

  #296865

  Hi guys,

  I have a procedure which iterates over the SQL Server linked servers (cursor) and goes to each to collect information. I purposely shut down my local databases because I should receive an "recent activity" message saying it failed to connect. However, the loop broke & I got no data (besides the first in the loop).

  I googled & tried to implement a TRY/CATCH but have just re-executed it and get an error again. Can anyone see what I'm doing wrong?

  DECLARE db_cursor CURSOR FOR

  select '"' + name + '"' from sys.servers

  where provider='SQLNCLI' and is_linked=1;

  OPEN db_cursor

  FETCH NEXT FROM db_cursor INTO @server

  WHILE @@FETCH_STATUS = 0

  BEGIN

  BEGIN TRY

  set @query = 'insert into dbo.DB_SAMPLE

  select *, GETDATE() from openquery(' + @server + ',''select @@SERVERNAME'')';

  exec(@query);

  END TRY

  BEGIN CATCH

  INSERT INTO connection_errors

  SELECT @server, ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE();

  END CATCH;

  FETCH NEXT FROM db_cursor INTO @server;

  END

  I thought if wrapped it in a try/catch it would insert a record in "connection_errors" and then continue the loop. It has inserted into the table but the loop did not continue. Any idea how to make it continue?

  Edit: In case specific errors are important I get these:

  OLE DB provider "SQLNCLI10" for linked server "ICSLA808" returned message "Login timeout expired".

  ---

  Dird

• Dird

  SSCertifiable

  Points: 5081

  More actions

  July 27, 2013 at 7:04 am

  #1636397

  Can't delete this thread~ The TRY/CATCH did work \o/ its just that the output file didnt print any text after the error.

  ---

  Dird