Post reply

Wracking my brain about 'looping through linked servers'

  • 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

  • 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!

  • 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!

    🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply