Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

I wrote something a while back, perhaps this link will help you.

http://www.mssqltips.com/sqlservertip/2017/script-to-check-all-your-linked-server-connections-for-sql-server/

I had a need to verify that the linked servers were working, especially after a server rebuild. And I had the same issues you are seeing with regards to using straight t-sql. So I ended up using Powershell in order to get the job done.

HTH

share|improve this answer
    
I was wondering if Powershell could help but that's not quite what I need; the fact that servers are down isn't the problem, we simply can't count on them always being up, I just need to be able to query all running servers and fail gracefully and continue the query if a server is down. It seems like it should be possible but SQL's exception handling doesn't seem to give me that option. –  Ben Brocka Sep 29 '11 at 18:25
    
exactly, that's why i used powershell. you can execute the script from a sql agent job and have it connect and check every linked server. –  SQLRockstar Sep 29 '11 at 19:05
    
Does this require a CMS Server be set? I'm using SQL Server 2005 databases unfortunately. The servers are linked through a central server so I need to use that one server's list, but I don't know if it will work without it actually being a CMS –  Ben Brocka Sep 29 '11 at 19:19
1  
I believe that script used a CMS, yes, but you should be able to find examples of Powershell scripts that use text files with server names as an input. You should also be able to query a table and use that as input for the server names. –  SQLRockstar Sep 30 '11 at 0:37
    
Thanks for the help, I learned a bit of powershell but couldn't get the connections to work, so I did it in PHP for now. Your resources were great though. –  Ben Brocka Sep 30 '11 at 16:08
add comment
up vote 1 down vote accepted

Unfortunately I couldn't get a pure SQL solution to work so I made a command line script to query each database in a PHP, as the PHP program can handle the timeout errors.

The queries were for a PHP web site so I considered the solution acceptable as it introduced the least complexity compared to a Visual Basic app (what others in-house suggested) and a Powershell script (a great idea but requires the most set up as no one here has used one).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.