I've been going back and forth with this question for a few days now.

If one instance in a multiserver query presents an error, no resultsets from any of the other instances are displayed, even if n "row(s) affected)" is displayed in the Messages tab.

This is particularly painful when querying dynamic management objects across server groups, because new DMV's and new DMV columns are being introduced in every version, sometimes with service packs.

• Is this a feature preventing you from using code that won't work against the group as a whole, saving you from accidentally assuming that all instances returned data?
• Or, is this a pain that causes you to need version-specific registered server groups to get at data from some instances?

There is no right answer to this question, only the opportunity to be aware of this behavior.

There is an old MSConnect item from 2007 for a similar issue that was marked as "won't fix" in 2011, so it is not a bug. I am not sure if there is any beneficial intent from MS to prevent you from getting any data when one instance in a group has a syntax error.

Imagine if, within a large list of production SQL Servers, a handful are of a prior SQL version where some utility scripts won't work.

Should I then create another registered server group for instances that can run sys.dm_os_volume_stats, or a group for instances that support the new columns added to sys.dm_exec_query_stats? No correct answer here either, in the absence of an option to allow for partial resultsets to be returned from multiserver queries, you will need to consider what suits your needs best.

Here's the steps to reproduce for a simple scenario:

1. Add a SQL 2008 instance and a SQL 2008 R2 instance to a new Registered Servers group.
2. Create a new multiserver query.
3. Attempt to query a DMF or DMV that was introduced in SQL 2008 R2, such as sys.dm_os_volume_stats. (Sample script below.)
4. The message table includes one error and one rows returned message, like below. No Results tab is returned, so the rows are not visible even though the query worked successfully on the SQL 2008 R2 instance.

sqldemo1\sql2008(domain\user): Msg 208, Level 16, State 1, Line 2Invalid object name 'sys.dm_os_volume_stats'.sqldemo1\sql2008r2(domain\user): (2 row(s) affected)

Sample script:

select distinct
vs.volume_Mount_point,
file_system_type,
   drive_size_GB = convert(decimal(19,2), vs.total_bytes/1024./1024./1024. ) ,
   drive_free_space_GB = convert(decimal(19,2), vs.available_bytes/1024./1024./1024. ),
   drive_percent_free = CONVERT(DECIMAL(9,2), vs.available_bytes * 100.0 / vs.total_bytes)
FROM
   sys.master_files AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.database_id, f.file_id) vs

I have confirmed this behavior in Microsoft SQL Server Management Studio 12.0.2000.8, 11.0.3128.0 and 10.50.4000.0.