Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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
share|improve this answer
    
Is sp_testlinkedserver not to be used? –  Sn3akyP3t3 Oct 13 '12 at 0:31

For Oracle, with a database link, select * from dual@dblink, again with appropriate error handling as per gbn's answer.

I don't think you will find one SQL statement for this that will work on both SQL Server and Oracle.

share|improve this answer
    
Ah, but if both queries are named the same on each server, then it would be a matter of calling that query as part of the standard set of queries, rather than having to remember which to use. Just a thought. –  jcolebrand Sep 9 '11 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.