List informations about SQL Servers through linked servers

Question

I'm finding out information from other instances through linked servers using the SERVERPROPERTY. Unfortunately the SERVERPROPERTY relates to the server from which I am now running the query.

Can you help me?

Query:

declare @instance_name nvarchar(50)
declare @command nvarchar(max)

declare instance_name_cursor cursor for
     select name 
     from msdb.sys.servers 
     where product = 'SQL Server'

open instance_name_cursor

FETCH NEXT FROM instance_name_cursor INTO @instance_name

while @@FETCH_STATUS = 0
begin
    select @command = 'declare @osversion nvarchar(50)

set @osversion = (select 
case
when windows_release = ''10.0'' then ''Windows Server 2016''
when windows_release = ''6.3'' then ''Windows Server 2012 R2''
when windows_release = ''6.2'' then ''Windows Server 2012''
when windows_release = ''6.1'' then ''Windows Server 2008 R2''
when windows_release = ''6.0'' then ''Windows Server 2008''
end
from sys.dm_os_windows_info )

insert into mydatabase.dbo.sql_server_info

select SERVERPROPERTY (''MachineName''),SERVERPROPERTY (''ProductVersion''),
SERVERPROPERTY (''Edition''),SERVERPROPERTY (''ProductLevel''),
cpu_count,(physical_memory_kb /1024),
(select maximum FROM ['+@instance_name+'].master.sys.configurations 
where configuration_id=1544),@osversion
from ['+@instance_name+'].master.sys.dm_os_sys_info'

     exec (@command)

     FETCH NEXT FROM instance_name_cursor INTO @instance_name
end

CLOSE instance_name_cursor
DEALLOCATE instance_name_cursor

Answer 1

You need to run the SERVERPROPERTY() function on the linked server, which you can do using OPENQUERY, as a simple example:

SELECT  MachineName
FROM    OPENQUERY([LinkedServer], 'SELECT MachineName = SERVERPROPERTY (''MachineName'')') x;

Then it is just a case of adjusting your query slightly to deal with this, so removing the cursor for demonstration terms you might have something like:

DECLARE @instance_name nvarchar(50) = 'LinkedServer';
DECLARE @command NVARCHAR(MAX);
DECLARE @commandBase NVARCHAR(MAX) = 
    'SELECT MachineName = SERVERPROPERTY (''''MachineName''''),
            ProductVersion = SERVERPROPERTY (''''ProductVersion''''),
            Edition = SERVERPROPERTY (''''Edition''''),
            ProductLevel = SERVERPROPERTY (''''ProductLevel''''),
            Maximum = (SELECT Maximum FROM master.sys.configurations WHERE configuration_id = 1544),
            OSVersion = CASE
                            WHEN wi.windows_release =''''10.0'''' THEN ''''Windows Server 2016''''
                            WHEN wi.windows_release = ''''6.3'''' THEN ''''Windows Server 2012 R2''''
                            WHEN wi.windows_release = ''''6.2'''' THEN ''''Windows Server 2012''''
                            WHEN wi.windows_release = ''''6.1'''' THEN ''''Windows Server 2008 R2''''
                            WHEN wi.windows_release = ''''6.0'''' THEN ''''Windows Server 2008''''
                        END
    FROM    master.sys.dm_os_sys_info AS si
            CROSS JOIN master.sys.dm_os_windows_info AS wi;';


SET @Command = CONCAT('SELECT * FROM OPENQUERY(', QUOTENAME(@instance_name), ', ''', @CommandBase, ''');');

INSERT mydatabase.dbo.sql_server_info (<Columns>)
EXECUTE sp_executesql @Command;

Then it is just a case of slotting this inside your cursor.