Sql-server – an industry standard method for verifying openquery connection is open prior to running queries

oraclesql servert-sql

I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community. It could very well cross over into stackoverflow as well.

I'm seeking an approved industry standard method of verifying an openquery connection is properly connected and responding appropriately before proceeding with running queries. The environment that is applicable to me would be nice to know if there is a specific answer, but I'm also looking for a generic approach as well if there is one for others and myself to benefit from. My environment here uses Microsoft SQL 2005 and Oracle in the openquery.

I found this post in a blog which shows how this can be done, but I'm not sure if there is a more efficient method for achieving the same results and without false indication or failure.

Best Answer

For Linked Servers in SQL Server ...

Run a SELECT @@SERVERNAME or some such in a stored procedure that contains a BEGIN TRY/CATCH block.

  • The stored procedure limits the scope in case it fails and for re-use
  • The TRY/CATCH is to suppress any error to the calling query
  • The stored proc uses an output parameter
Related Question