linked server availability question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

linked server availability question

Author

Topic

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-30 : 17:58:40

is there any way to determine the status (up or down) of a linked server prior to attempting to query from it? I have a report that when run queries several linked servers. It works 99% of the time, unless a linked server is unavailable. Then it completely sh!ts the bed (pardon my language).

I have done the following:

1. Created several linked servers (they all work when created)
2. Test my distibuted query, it works woohoo!
3. Try to make the query break by either downing one of the linked instances, or pointing a linked server to a system where SQL doesn't exist.
4. re-run the query only to have it fall apart. Instead of hitting the remaining linked servers with the query, it stops after failing to connect to the 2nd.

For this report (a backup status report), I would be ok with it just skipping the linked server that isn't up.

I get this error:

Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

or this error:

Server: Msg 14, Level 16, State 1, Line 1
Invalid connection.

I have not figured out a way in TSQL to handle these errors. I'm thinking now that I will need to try and determine the availability status of a linked server from an extended stored proc, but I would rather take the easy way out if it exists.

Anyone have any ideas on how I can try to solve this? Thanks for any help.

-ec

mfemenel
Professor Frink

1421 Posts

Posted - 2004-08-30 : 18:15:34

Maybe this is too simplistic, but couldn't you do a select getdate() from an openquery statement. If @@error=0, you can query the table, if not you move on to the next step.

Mike
"oh, that monkey is going to pay"

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-30 : 18:20:46

quote:
Originally posted by mfemenel

Maybe this is too simplistic, but couldn't you do a select getdate() from an openquery statement. If @@error=0, you can query the table, if not you move on to the next step.

I like simple. I'll give it a try, thanks for the input.

-ec

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-30 : 18:24:39

Or maybe this..

DECLARE @i int
EXEC @i = master..xp_cmdshell N'osql -S SIRIUS -E -Q "SELECT 1"', NO_OUTPUT
IF @i = 0
	PRINT 'lets hit this one'
ELSE
	PRINT 'lets skip this sucker'

suppesedly it's all winauth and name of server same as netname.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-30 : 18:41:25

quote:
Originally posted by mfemenel

Maybe this is too simplistic, but couldn't you do a select getdate() from an openquery statement. If @@error=0, you can query the table, if not you move on to the next step.

Even with using OPENQUERY I cannot get past the error when the linked server does not exist. After hitting that error, the code stops completely.

I will try the method that rockmoose suggested.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-30 : 20:16:02

I got rockmoose's suggestion to work. Thanks!

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources