how to write this query accordingly

• Mad-Dog

  SSCertifiable

  Points: 6099

  April 10, 2019 at 2:36 pm

  #3092278

  Hi,

  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?

   

  THX

  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

  April 12, 2019 at 1:31 pm

  #3156562

  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

  April 12, 2019 at 2:13 pm

  #3157495

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

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com

• Jeff Moden

  SSC Guru

  Points: 994866

  April 12, 2019 at 6:01 pm

  #3165879

  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)