Using SQL Server 2005 databases and SQL Server Management Studio 2008, I need to query a single value from each of n linked databases at a regular interval. The servers are physically remote and I can basically count on a timeout, attempts to create a query that completes have failed. Every attempt fails when a server times out, I've specifically gotten the 'Login timeout Expired' error.
Ideally I need a stored procedure that generates a row or table with a column or row for each server and a value indicating what my value is in the remote database, or null if I get a timeout (the value on the remote database cannot be null).
My first thought to gather the data was
select
(SELECT TOP 1 [value]
FROM
[RemoteServer1].[DB].[dbo].[Table]) as
RemoteServer1,
(SELECT TOP 1 [value]
FROM
[RemoteServer2].[DB].[dbo].[Table]) as
RemoteServer2,
...
Which generates a single row with all the values I need and indexes I can use to report the needed values. But a single time out in one select statement kills the whole query (it does execute correctly if all servers are reached). I can safely assume that at some point in the query at least one server will time out.
I tried an exception, but it doesn't seem to work (I've never trued to use exceptions in SQL Server before). I tried this in place of each subquery:
begin try
(SELECT TOP 1 [Value]
FROM
[RemoteServer1].[DB].[dbo].[Table]) as RemoteServer1
end try
begin catch
select null as RemoteServer1
end catch,
But the timeout still causes the whole query to fail, I think it's because try only works if the connection doesn't fail?
Is there some way this can work? The only other solution I can think of would be a stored procedure on each remote database to send this record so it's all on our local database (which we can depend on reaching) but that would introduce much more complexity than this query is worth.
