Check If A Connection Is Active

[elmo29]

Is there any way to check if a connection between 2 SQL Servers exists.

For instance I don't want a sproc to be run unless a connection exists (i.e it could be broken by resetting the computer etc). Is there any way I can do this within code so I can deal with this type of scenario??

 

[tlbroadbent]

When you say "connection between 2 SQL Servers" are you referring to a linked server? If so you can check the sysservers table for the existence of a linked server entry.

On server1 you could use the following T-SQL to check if server2 is linked.

If exists (Select * From sysservers where srvname = N'server2')
Begin
<your logic here - server2 link exists>
End
Else
Begin
<exeception logic here - server2 isn't linked>
End
Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes

 

[elmo29]

What I am really talking about is if the connection goes down between the linked servers - like when you reboot the machine etc, it would be good it I could determine that the link has been lost and redirect the information elsewhere.

it seems to me the code you have just checks that there should be a linked server, it doesn't test that the link is live????? is that right??