LinkedServer version

Darrylw99

I am trying to work through a table of linked server names to get their version and productversion Here is the code I'm using but I need to be able to get the values and use them elsewhere. I cannot get them into parameters. Also despite it populating the output with a list it isn't for each server. I mean its exactly the same info for every server. But its wrong its showing EG:

BUT should be

Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)
Jan 5 2018 22:11:56
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

BEGIN
DECLARE @srv SYSNAME, @db SYSNAME, @exec NVARCHAR(1024);
DECLARE @serverName NVARCHAR(100)
DECLARE @i INT = 1
DECLARE @ServerCount INT=0
DECLARE @retval nvarchar(1000)
DECLARE @ParmDefinition nvarchar(500);
DECLARE @sSQL nvarchar(500);

SET @ServerCount = (SELECT COUNT(*) FROM SQLServerList.dbo.ListOfServers)
SET @db = N'tempdb';

WHILE (@i <= @ServerCount)
BEGIN
SET @serverName = (SELECT serverName FROM SQLServerList.dbo.ListOfServers where id = @i)
IF @serverName IS NOT NULL
BEGIN
SET @exec = N'' + QUOTENAME(@serverName) + N'.' + N'' + QUOTENAME(@db) + N'.sys.sp_executesql ';

		SELECT @sSQL = N'SELECT ''' + cast(@serverName as nvarchar) + ''',@@VERSION as version,SERVERPROPERTY(''ProductVersion'') AS ProductVersion  ';

		SET @sSQL = @exec + ' ' + @sSQL
		EXEC sp\_executesql @sSQL;

	END		
	SET @i = @i + 1			
END

END

Mycroft Holmes

Check that the ID on the ServerList table is the same as i. i will be 1 then 2 then 3 etc I bet the ID field in servertables does not match. Try using a cursor and looping through the servertable.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP