how to write this query accordingly

Question

how to write this query accordingly

Mad-Dog

SSCertifiable

Points: 6117
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>'

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 2

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: 1004403 More actions · Answer 3

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.

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