SELECT @@ServerName on remote server returns local @@ServerName

Question

I have two servers [TESLABSQL01T] & [TESLABSQL02T] and I want to monitor their connections remotely.

A user yesterday suggested me a query that could help me out and the query is indeed working:

DECLARE @ServerName varchar(50), @DynamicSQL NVARCHAR(MAX)
DECLARE @myTableVariable TABLE (id INT, ServerName varchar(50))
insert into @myTableVariable values(1,'[TESLABSQL01T]'),(2,'[TESLABSQL02T]')
-- select * from @myTableVariable

Declare VarCursor cursor for
Select ServerName from @myTableVariable
Open VarCursor
FETCH NEXT FROM VarCursor INTO @ServerName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @DynamicSQL='SELECT @@ServerName AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM '+@ServerName+'.master.sys.databases sd
    LEFT JOIN '+@ServerName+'.master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame'
    

    EXEC (@DynamicSQL)

    FETCH NEXT FROM VarCursor INTO @ServerName
END
CLOSE VarCursor
DEALLOCATE VarCursor

The problem is that the qiery is returning [TESLABSQL01T] as @@ServerName on my local server but is also returning [TESLABSQL01T] for the remote @@ServerName.

This is wrong, it should return [TESLABSQL02T].

Answer 1

@@ServerName will always give you the local server name unless you execute the query via OPENQUERY.

If you want to use the same variable @ServerName as you use for the dynamic query (the linked server name) then just change your dynamic SQL to use it.

    SET @DynamicSQL = 'SELECT ' + QUOTENAME(@ServerName, '''') + ' AS [ServerName]

If you want the name that the remote server uses for itself, then you could use a correlated subquery within the dynamic part:

    SET @DynamicSQL = 'SELECT (SELECT name FROM sys.servers srv WHERE srv.server_id = 0) AS [ServerName]