Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 14 May 2014

Linked Server in SQL Server

EXEC sp_addlinkedserver  
   @server=N'LS',             -- Remote Computer Name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'WELCOME-PC\MSSQL05';  -- Server Name And Instance

If the remote SQL Server does not have an instance name, then the @datasrc parameter nee only contain the remote server name and not the instance.


select * from Linkedservername.Databasename.Schemaname.Tablename

(Ex:)

select * from LS.testdb.dbo.Attribute


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

---------------------------To know tables under linked server---------------------

sp_tables_ex 'LS'