2

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.

2 Answers 2

4

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

5
  • 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, 2011 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. Sep 29, 2011 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, 2011 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. Sep 30, 2011 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, 2011 at 16:08
1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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