Get one linked server property

Question

I use SQL Server 2014, I also have a linked SQL Server configured on it, and I need to get a particular linked server property via TSQL. The property is 'remote proc transaction promotion'. I set this option through the next code

EXEC sp_serveroption 'LinkedServer', 'remote proc transaction promotion', 'FALSE'

but I do not know how I can read the value of this option via T-SQL. Any help is appreciated.

Answer 1

The sys.servers system catalog view has a record for each registered linked or remote (for backward compatibility) server. The is_remote_proc_transaction_promotion_enabled column corresponds to the remote proc transaction promotion option of the sp_serveroption stored procedure. This column is of the bit data type, with true represented by 1 and false by 0.

select is_remote_proc_transaction_promotion_enabled from sys.servers where name = 'LinkedServer'