how to write this query accordingly

• Mad-Dog

  SSCertifiable

  Points: 6104

  More actions

  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: 396560

  More actions

  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: 719974

  More actions

  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: 996661

  More actions

  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.
  "Change is inevitable... change for the better is not".
  "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
  

  ---

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