SQL query to multiple servers fails

Question

I try to run this query against multiple registered SQL Servers with SSMS:

SELECT DISTINCT(volume_mount_point), 
    total_bytes / 1048576 AS Size_in_MB, 
    available_bytes / 1048576 AS Free_in_MB,
    (SELECT ROUND(CAST(available_bytes / 1048576 * 1.0 as FLOAT) / CAST(total_bytes / 1048576 * 1.0 AS FLOAT) * 100, 2)) AS FreePercentage
FROM 
    sys.master_files AS f 
CROSS APPLY 
    sys.dm_os_volume_stats(f.database_id, f.file_id)
GROUP BY
    volume_mount_point, total_bytes / 1048576, available_bytes / 1048576  
ORDER BY 
    4

Some of the servers have SQL Server 2008 R2 RTM product level which doesn't know "sys.dm_os_volume_stats" and whole query crashes reporting:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_os_volume_stats'.

I tried to add a condition before the main SELECT, but it doesn't work:

  DECLARE @ProductLevel varchar(128)  
  SET @ProductLevel = CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductLevel'))
  IF @ProductLevel not like 'RTM'
    BEGIN...

I also tried to separate results by this documentation To change the multiserver results options

To change the multiserver results options In Management Studio, on the Tools menu, click Options.

Expand Query Results, expand SQL Server, and then click Multiserver Results.

On the Multiserver Results page, specify the option settings that you want, and then click OK.

Any other ideas?

Answer 1

The IF condition won't be evaluated if the DMV doesn't exist because the entire batch fails at compilation time so the IF statement is never executed.

One workaround is to wrap the query in conditional dynamic SQL:

IF CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS varchar(20)),4) AS int) > 10 OR 
    (CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS varchar(20)),4) AS int) = 10
     AND CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS varchar(20)),3) AS int) = 5
     AND SERVERPROPERTY('ProductLevel') <> 'RTM')
BEGIN
EXEC sp_executesql N'
SELECT DISTINCT(volume_mount_point), 
    total_bytes / 1048576 AS Size_in_MB, 
    available_bytes / 1048576 AS Free_in_MB,
    (SELECT ROUND(CAST(available_bytes / 1048576 * 1.0 as FLOAT) / CAST(total_bytes / 1048576 * 1.0 AS FLOAT) * 100, 2)) AS FreePercentage
FROM 
    sys.master_files AS f 
CROSS APPLY 
    sys.dm_os_volume_stats(f.database_id, f.file_id)
GROUP BY
    volume_mount_point, total_bytes / 1048576, available_bytes / 1048576  
ORDER BY 
    4;
'
END;