0

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>’
1
  • 1
    Hi and welcome to DBA.SE. Do you simply need the select query text for each of the linked servers, or actually execute a query against them? In other words, do you need to execute this part SELECT CONVERT(NVARCHAR(MAX),column_name) from [@server].db_name.dbo.table_name Or is creating the script for it enough? Apr 11, 2019 at 7:15

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.