linked servers

[ronmon]

Does anyone know the code to check if a linked server connection is up i want to run a query against a table on a linked server but would like to stick it inside an if statment chesking first to see if the connection is up

 

[Bastien]

use ping. write a small file that you can activate from your procedure and see if it returns a positive response...then execute your query


Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[ClaireHCM]

Try

select * from sysservers
or
sp_linkedservers

 

[ronmon]

I am still stuck on it the sp_linkedserver just returns a list of servers that are setup but i want to check to see if the connection between the servers is still available i.e. I want to do a
select * from whlink.dbo.rmdswh.orders_table
but also stick in some code that will return a a value if the connection is up and another value if sonnection is down i was looking at the ping thing but i have to confess i have no idea where to begin with it has anyone got any ideas

 

[ecobb]

You might could try somenthing like:

DECLARE @Status Varchar(50)

IF EXISTS (select * from whlink.dbo.rmdswh.orders_table)
BEGIN
  SET @Status = "Server Is Up"
END

ELSE
BEGIN
  SET @Status = "Server Is Down"
END
...Blah...blah...blah...

Put something like that in a Stored Procedure (yeah, it needs some work, but you get the general idea. Maybe someone else here can help 'cause my T-SQL is extrememly rusty!), have it return the results of @Status, and then just run the procedure whenever you want to check a Linked Server. With a little tweaking you might could set it up to check all of your linked servers at one time.





Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher