how to write this query accordingly 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'&lt;TD&gt;’ + ISNULL( + ‘&lt;p class=”datarows”&gt;’ + (SELECT CONVERT(NVARCHAR(MAX),column_name) from [@server].db_name.dbo.table_name),'&lt;p class=”datarowserror”&gt;Not Avail’) + N'&lt;/p&gt;&lt;/TD&gt;’
If you only need the SELECT statements for each server

SELECT 'SELECT CONVERT(NVARCHAR(MAX),column_name) from ' + QUOTENAME(name) '.db_name.dbo.table_name;' FROM sys.servers where server_id != 0


This gives me the statements for my two linked servers

SELECT CONVERT(NVARCHAR(MAX),column_name) from [Server1].db_name.dbo.table_name;

SELECT CONVERT(NVARCHAR(MAX),column_name) from [Server2].db_name.dbo.table_name;


If you need to execute the statements at once, you could use a cursor and dynamic SQL. 

DECLARE @server varchar(50)
DECLARE @SQL NVARCHAR(MAX)
DECLARE C CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.servers where server_id != 0
OPEN C
FETCH NEXT FROM C INTO @server

WHILE @@FETCH_STATUS = 0
BEGIN   
SET @SQL =
'
SELECT CONVERT(NVARCHAR(MAX),column_name) from '+QUOTENAME(@server)+'.db_name.dbo.table_name;'
--PRINT(@SQL)
EXEC(@SQL)
FETCH NEXT FROM C INTO @server
END
CLOSE C
DEALLOCATE C