how to write this query accordingly

  • Mad-Dog


    Points: 6099


    i need this query to run against 20 instances from main server that have linked server to those 20 instances.i want to change the [@server]  to the right instance via cursor and make the SELECT statement to work.

    how can i write the query accordingly?



    declare @server varchar(50)

    set @server = 'servername\instnacename'

    SELECT N'<TD>' + ISNULL( + '<p class="datarows">' + (SELECT CONVERT(NVARCHAR(MAX),column_name) from [@server].db_name.dbo.table_name),'<p class="datarowserror">Not Avail') + N'</p></TD>'

  • Grant Fritchey

    SSC Guru

    Points: 395586

    Personally, I wouldn't do this through T-SQL. I'd use Powershell. Here's an example.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    You could query sys.sysservers in master to get the linked servers, then with a cursor you can loop through them.

  • Jeff Moden

    SSC Guru

    Points: 994866

    Grant Fritchey wrote:

    Personally, I wouldn't do this through T-SQL. I'd use Powershell. Here's an example.


    And then scream as the Network folks draw and quarter you for screwing up internal security.


    Lordy, Grant... did you move from subs to grey-hounds??? 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 4 (of 4 total)

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