Test linked server connection settings...

Have you ever encountered a scenario in SQL Server 2000 where you want to test the connectivity of a linked server configuration via TSQL? This was not possible easily and best handled from client side. But we have now a system stored procedure "sp_testlinkedserver" in SQL Server 2005 that allows you to test linked server connection. This stored procedure takes a linked server name as parameter, tests the connectivity and returns 0 for success & 1 for failure. You can find more details about this stored procedure in the SQL Server 2005 Books Online. Here is how a sample call can look like:

declare @srvr nvarchar(128), @retval int;
set @srvr = 'my_linked_srvr';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

The reason for the try...catch block is left as an exercise to readers!

--
Umachandar Jayachandran

Comments

• Anonymous
  August 11, 2005
  Hi
  I am using SQL Server 2000 and want to test my connection to my linked server which is also running SQL Server 2000. Is there any way to test the connection ...........
  
  Thanks in advance
  
  Ganesh

• Anonymous
  August 16, 2005
  Unfortunately, there is no easy way to do this in SQL Server 2000 without executing some command on the linked server. And trapping errors is also difficult in this case.
  
  --
  Umachandar

• Anonymous
  October 25, 2005
  How about linking (and testing) a SQL 2000 server to a SQL 2005 server (different domains)?
  
  Thanks!

• Anonymous
  October 30, 2005
  The comment has been removed

• Anonymous
  August 24, 2006
  In the MS SQL Server Management Studio... I can't get the views and tables to show up under the list of linked servers like 2000 would do....any ideas on that?

• Anonymous
  September 25, 2006
  PingBack from http://chaespot.com/mssql/2006/09/25/oracle-sql-migration-1-performance-client-0/

• Anonymous
  December 12, 2006
  Know this is an old thread but for people on the same journey as me trying to find an answer this might help Found that for SQL 2000 the code above does not work and I get an error which the @@error did not trap. However found some SQLDMO code at http://www.sqldbatips.com/displaycode.asp?ID=38 whic works in the same wasy as the SQL 2005 procedure. Jim

• Anonymous
  March 26, 2008
  Yes, Oracle client is a must for linked servers. You can check out the requirements at this link: http://support.microsoft.com/kb/280106 http://msdn2.microsoft.com/en-us/library/ms190618.aspx http://msdn2.microsoft.com/en-us/library/ms189063.aspx You can use SQLCLR facility to access Oracle. You will have to use the Oracle .NET provider for example. In any case, you will need to install additional software on the server to access Oracle. Thanks Umachandar

• Anonymous
  March 26, 2008
  Hi, Thanks a lot. This information is very much useful for us. Can we use Oracle Light Version i.e. "ORACLE INSTANT CLIENT" instead of Oracle Client Software to connect Oracle using Linked Server in SqlServer 2005. This Light Version seems to be license free version. I would like to try this Light version. Can you please share your views, similar instances with us Thanks & Regards, Palani

• Anonymous
  April 20, 2008
  Hi, What are the major differences in the behaviour,usage of linked servers between sql server 2000 and sql server 2005?. I have implemented linked servers to connect Oracle in sql server 2005. I would like to know whether it will work fine in sql server 2000 with SP4 release?. Please give us your opinion Regards, Palani

• Anonymous
  March 03, 2009
  A great way to test if the linked server is setup correctly in 2000 or 2005 is as follows: sp_tables_ex linkservername sp_catalogs linkservername If you can display the tables and the catalog then you are good to go usually. Hope this helps!

• Anonymous
  June 08, 2009
  PingBack from http://quickdietsite.info/story.php?id=5112