Sql-server – how to write this query accordingly

sql serversql-server-2016t-sql

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>’

Best Answer

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
Related Question