Check if Linked Server is Running

[bspiglejr]

I have a stored procedure that references another stored procedure on a linked server. I would like to check if the linked server is running prior to accessing the stored procedure. Everything I have tried returns either message 11 or 17 and terminates the stored procedure. I would like to do some cleanup before the termination. Is there any way to check this?

ben

[skhanal]

There may not be a direct way to do this. I would try using SQL DMO SQLServerObject's Connect method and based on success or failure I would proceed.

Or I could use odbcping and grab the output in a file, parse the output to get the status.

[bspiglejr]

Thanks for the reply. I decided I didn't want anything that elaborate. One piece of information I left out is that the stored procedure runs from a scheduled job in SQL Server. I just added a step before the one that runs the procedure that accesses a table on the remote server. If that fails then the stored procedure doesn't run at all - negating the need for cleanup. It's not perfect but it should catch it almost all the time...

ben