TSQL Check Database Instance Online

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.

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

Query sys.sysdatabases through the linked server (you need permissions), here are the values for the status column

1 = autoclose (ALTER DATABASE)

4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)

8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)

16 = torn page detection (ALTER DATABASE)

32 = loading

64 = pre recovery

128 = recovering

256 = not recovered

512 = offline (ALTER DATABASE)

1024 = read only (ALTER DATABASE)

2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)

4096 = single user (ALTER DATABASE)

32768 = emergency mode

4194304 = autoshrink (ALTER DATABASE)

1073741824 = cleanly shutdown