Linked Servers

• guynethers

  SSC Journeyman

  Points: 99

  More actions

  November 7, 2010 at 1:12 pm

  #240913

  Hi

  I have various scripts that use linked servers to obtain data from various distributed systems - all ms sql 2005.

  One such script uses dynamic sql and passes the linked server through as a parameter and then loops through all required linked servers until the script has completed.

  The issue I have is that if the linked server in the middle of the list of linked servers failed to connect the rest of the query would not run.

  I have adopted this approach in order to avoid create a script per linked server and overcoming this issue using the task manager - if fail move to next step.

  Please can someone advise me how I can check to see if a linked server is connecting successfully using sql code first before progressing with the rest of the process but also more importantly if it is not connecting how to identify this and move on to the next step.

  Thanks

• Adiga

  One Orange Chip

  Points: 27284

  More actions

  November 7, 2010 at 10:42 pm

  #1247510

  You may include some error handling in the query that you are using. TRY...CATCH would be better approach. More details here

  Pradeep Adiga
  Blog: sqldbadiaries.com
  Twitter: @pradeepadiga