Wracking my brain about 'looping through linked servers'

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  October 4, 2011 at 3:12 am

  #259894

  I'm working on a Job History management system to get job statuses from severa instances of SQL2005. We don't have SQL2008, so CMS is not available. Basically I want something like

  select distinct j.Name as "Job Name",j.enabled from @LinkedServer.msdb.dbo.sysjobs

  Obviously I want more info, but as an example this will do.

  I can make this work in several ways with dynamic sql. There are several reasons why this is not a very good solutions. I have found numerous scripts, and they all do basically the same: build a cursor (or WHILE-loop) and create a dynamic sql-statement for each linkedserver you wish to call. This will work fine, I am sure. I also found ways to use a SSIS-package to accomplish the same. However, I am bitten by the avoid-cursor bug, all because of Barry Young's articles, and I am wracking my brain to see how this can be done without using dynamic sql, or at least without using a cursor or WHILE-loop and I can't find it. I'm beginning to think it's not possible.

  I read an excellent article here: http://www.sommarskog.se/dynamic_sql.html, but the solutions given here do not seem right to me.

  I think I am going with the SSIS solution, but I still would like to know: is there an efficient way to loop through linked servers?

  Greetz,
  Hans Brouwer

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 4, 2011 at 5:50 am

  #1390072

  for me, cursors are ok in certain situations...when dealing with metadata items like you are.

  my rule of thumb is: if it's in one database, no cursors allowed.

  so the corallary is if the data is in multiple databases, a cursor( or something that builds a stack of commands or statements) is permissible.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Panders

  Right there with Babe

  Points: 772

  More actions

  October 4, 2011 at 6:58 am

  #1390104

  As your attaching across servers there should be no problem with your loop (while or cursor) Id lean towards cursor but that my preference!