TSQL Check Database Instance Online

Question

What is the best way to check if an instance of Microsoft SQL Server is up? I have a view that spans two instances of Microsoft SQL Server but before querying it in a stored procedure I would like to verify that the second instance of the server is up with a fallback option of just querying local data.

The second instance is a linked server.

Currently I'm considering a SQL CLR function that can attempt to open a connection with a shorter timeout but I'm wondering if it's something that can be done directly in Transact SQL.

Solution

How about just validating that the database you are interested in connecting to is online?

select 
    name
from sys.databases
where name = 'DatabaseName'
    and state = 0 --Database is online

For the full reference of available columns for the sys.databases catalog view see Books Online: sys.databases

EDIT: Fix SQL query