Welcome to MSDN Blogs Sign in | Join | Help

SQL Server Engine Tips

Guidelines, Best Practices, TSQL and SQL Programming Tips & Tricks.
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

Published Tuesday, June 07, 2005 9:09 PM by SQL Server Engine Team

Filed under:

Comments

# re: Test linked server connection settings... @ Friday, August 12, 2005 3:16 AM

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

Ganesh

# re: Test linked server connection settings... @ Tuesday, August 16, 2005 5:07 PM

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

SQL Server Engine Team

# re: Test linked server connection settings... @ Tuesday, October 25, 2005 4:59 PM

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

Thanks!

BobH

# re: Test linked server connection settings... @ Sunday, October 30, 2005 2:49 PM

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
begin
print 'Error'
end
go
exec('select * from openquery(some_servr, ''select 1'')')
if @@error <> 0
begin
print 'Error'
end

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.

--
Umachandar

SQL Server Engine Team

# re: Test linked server connection settings... @ Thursday, August 24, 2006 4:51 PM

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?

James

# Microsoft &raquo; Blog Archives &raquo; Oracle SQL Migration (1) Performance - Client (0) @ Monday, September 25, 2006 4:13 PM

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

Microsoft » Blog Archives » Oracle SQL Migration (1) Performance - Client (0)

# re: Test linked server connection settings... @ Tuesday, December 12, 2006 9:59 AM

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

Jimf

# re: Oracle Client Software requirements... @ Wednesday, March 26, 2008 3:10 PM

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

SQL Server Engine Team

# re: Test linked server connection settings... @ Thursday, March 27, 2008 12:08 AM

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

pvr_in_msdn

# Linked Server in SQL SERVER 2000 and SQL SERVER 2005 @ Monday, April 21, 2008 5:07 AM

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

pvr_in_msdn

# re: Test linked server connection settings... @ Tuesday, March 03, 2009 11:21 AM

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!

verena_techie

# SQL Server Engine Tips Test linked server connection settings | Quick Diets @ Tuesday, June 09, 2009 7:18 AM

PingBack from http://quickdietsite.info/story.php?id=5112

SQL Server Engine Tips Test linked server connection settings | Quick Diets

Anonymous comments are disabled
Page view tracker