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!

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.

Republished from SQL - My Best Friend [58 clicks].  Read the original version here [32134 clicks].