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 (12)

  1. Ganesh says:


    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


  2. sqletips says:

    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.


  3. BobH says:

    How about linking (and testing) a SQL 2000 server to a SQL 2005 server (different domains)?


  4. sqletips says:

    Due to lack of exception handling and implementation of OPENQUERY/OPENROWSET/OPENDATASOURCE interfaces it is not possible to do it cleanly. The afore-mentioned interfaces remain the same in SQL Server 2005 also and they perform their validation at compile-time itself. So for example, if the linked server name is incorrect or the parameter to OPENROWSET is invalid you will get an error immediately. You cannot capture such errors within TRY…CATCH also. You need to enclose it in dynamic SQL so it can be caught in the same batch as the TRY…CATCH. EX:

    — below will fail at compile-time itself

    — so the try..catch never executes

    begin try

    select * from openquery(missing_server, ‘select * from sys.tables’)

    end try

    begin catch

    end catch

    — using dynamic sql will help since the query

    — is evaluated only when the dynamic SQL statement is executed

    begin try

    exec(‘select * from openquery(missing_server, ”select * from sys.tables”)’)

    end try

    begin catch

    end catch

    This also holds true in SQL Server 2000. Compare code below:

    select * from openquery(some_servr, ‘select 1’)

    if @@error <> 0


    print ‘Error’



    exec(‘select * from openquery(some_servr, ”select 1”)’)

    if @@error <> 0


    print ‘Error’


    So the bottom-line is that you could use dynamic SQL to run the test statement against remote server and catch any errors.

    Now, as for your question about different domains there is lot of complexity involved depending on how you want to establish the connection. If you are using SQL logins then it is straight forward assuming the firewall or network settings allow communication between the servers. In case of Windows authentication, you may have to enable constrained delegation (Windows Server 2003) or security delegation depending on service account configurations and from where the clients connect. See BOL for discussion on these topics.


  5. James says:

    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?

  6. Jimf says:

    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


    whic works in the same wasy as the SQL 2005 procedure.


  7. sqletips says:

    Yes, Oracle client is a must for linked servers. You can check out the requirements at this link:




    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.



  8. pvr_in_msdn says:


    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,


  9. pvr_in_msdn says:


    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



  10. verena_techie says:

    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!