SQL SERVER – How to create Linked Server in SQL Server and its important Commands.

 Linked Server provide option to access / connect data to other Data source like MS Access, Oracle, My SQL etc. To access other data source into SQL server instance. We can use Linked server option. 

Linked Server Information stored into Master Database.

To create Linked Server we have to follow below steps-

Using User Interface-

1.       Open SSMS(SQL Server Management Studio) and open server object(If not found this option then find it in ‘View’ menu )

2.       Then Right click on the Linked server and click on the ‘New Linked server’

3.       Then we need to provide required Stuff(Server name, Instance Name, Default database name, Data source type etc) and we can access data from newly connected Data Source.

Using SQL Query –

SQL Server has provided below stored procedure to createvLinked server.

SP Name - Sp_AddLinkedServer

Example-

EXEC sp_addlinkedserver

   @server = N'Jai_Linked_Server_Test1',

   @provider = N'Microsoft.ACE.OLEDB.12.0',

   @srvproduct = N'OLE DB Provider for ACE',

   @datasrc = N'C:\MSOffice\Access\Samples\MS_ACCESS_DATABASE.accdb';

GO

Important commands-

Tests the connection to a linked server: 

   sp_testlinkedserver Jai_linked_server_test1

       To know linked servers information:

            Select * from sys.servers

      To access table from different server:

          Select * from  Linkedservername.Databasename.dbo.Tablename

      To Delete created linked server:

     IF OBJECT_ID('Jai_linked_server_test1') IS NOT NULL

     EXEC master.sys.sp_dropserver 'Jai_linked_server_test1','droplogins'