Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 7 February 2013

To Test the connectivity of Linked Server and See list of linked server in SQL

To Test the connectivity of Linked Server and See the list of linked Server in SQL:

Method 1:





Scripts:

EXEC sp_addlinkedserver   'SEATTLESales','SQL Server' ---------To add the linked server list

select * from sys.servers---------To see the linked server list

exec sp_linkedservers---------To see the linked server list

1)exec sp_testlinkedserver linkserverame----------To test the linked server connectivity


2)sp_tables_ex linkservername----------To test the linked server connectivity


3)----------To test the linked server connectivity using script-------------
declare @srvr nvarchar(128), @retval int;
set @srvr = 'linkedservername';
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 );

Method 2:



How to test linked server connectivity?
Execute the following Microsoft SQL Server Transact-SQL script to create a stored procedure to perform a ping test for the connectivity of a linked server:
USE AdventureWorks2008;
GO
CREATE PROC procPingLinkedServer  @LinkedServer sysname
/*  RETURNS
        1 --> Connection success
        0 --> Connection failure
*/
AS
BEGIN
DECLARE @Command nvarchar(1048)
SET NOCOUNT ON;
CREATE TABLE #PingTest (  CmdResultBuffer varchar(128));
SET @Command = 'ping '+ @LinkedServer
PRINT @Command
INSERT #PingTest
    EXEC master..xp_cmdshell   @Command;

IF EXISTS ( SELECT 1 FROM #PingTest WHERE CmdResultBuffer LIKE '%TTL%' )
    RETURN 1;
ELSE
    RETURN 0;


drop table #PingTest
END
GO

-- Test linked server connectivity
DECLARE @Connection int
EXEC @Connection = procPingLinkServer 'LINKEDSERVERALPHA'
SELECT ConnectionStatus = @Connection