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?

Dan Guzman · Accepted Answer · 2019-05-12T21:59:15.227

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;

edited May 12 '19 at 21:59

answered May 12 '19 at 21:21

Dan Guzman

43,250
3
46
71

I've just tried to run your query against two test servers (both non RTM) and it returned only this: "server1\DB1(login): (4 row(s) affected)" – Josef B. May 12 '19 at 21:28
1

@JosefB., forgot to include the product level check too so I added it. – Dan Guzman May 12 '19 at 21:33
Now I understand where you are aiming! I just need to adjust your query a little bit. The Product version of SQL2008 R2 RTM is 10.50.1600.1 and Product version of SQL2008 R2 SP2 is 10.50.4042.0, where the sys.dm_os_volume_stats is integrated. So the condition "ProductVersion >= 11" cannot be met with a lot of SQL servers in my enviroment, but anyway your query will help me a lot. I'll come back as I get fully working query. Thank you! – Josef B. May 12 '19 at 21:50
@JosefB., sorry I didn't have a SQL 2008 R2 RTM instance handy to test. I tweaked the query with a condition I think will work. – Dan Guzman May 12 '19 at 21:59
at first - please don't apologize - I'm happy you are helping me. Second thing, I simplified the condition to this "IF (EXISTS (SELECT * FROM sys.all_objects WHERE name = 'dm_os_volume_stats'))". Third thing - it is not necesary to use executing it as **stored procedure** but still I have to set parameter for Tools\Multiserver results\Merge results to False – Josef B. May 16 '19 at 12:12

SQL query to multiple servers fails

1 Answers1