T-SQL Using Unsupported Column In Query

Question

I'm trying to query cluster node information from SQL Server 2008 R2 and up using the following query.

select
nodename node,
status status_value,
status_description status_text,
case when is_current_owner = 1 then 'true' else 'false' end is_current_owner
from sys.dm_os_cluster_nodes

This works fine on SQL Serve 2012 and up but on 2008 R2 according to the sys.dm_os_cluster_nodes page it looks like the status and status_description aren't available.

Is it possible to run the query on 2008 R2 and fill in values for the unsupported columns? I tried using case statements to detect the SQL Server Version and print a default value else the 2012 column. It seems like if the status or status description columns are present anywhere in the query, then the query fails with invalid column.

Answer 1

You could ask the server what version it is and do something different depending on the response.

DECLARE @sql NVARCHAR(MAX) = ''

IF (SELECT left(cast(SERVERPROPERTY('productversion') as char(10)),2) )='12'
BEGIN

SELECT @sql = @sql +

'
select
nodename node,
status status_value,
status_description status_text,
case when is_current_owner = 1 then ''true'' else ''false'' end is_current_owner
from sys.dm_os_cluster_nodes'
END

ELSE
BEGIN
SELECT @sql= @sql +
'
select
nodename node,
'''' as status_value,
'''' as status_text,

case when is_current_owner = 1 then ''true'' else ''false'' end as is_current_owner
from sys.dm_os_cluster_nodes

'
END

select @sql 
exec sp_executesql @sql