SQL SERVER – Tips of Linked Server in SQL Server

        Linked Server is providing facility 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. To creating 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 Stored procedure to creating Linked server.

Name - Sp_AddLinkedServer

Example-

EXEC sp_addlinkedserver

   @server = N'SEATTLE Mktg',

   @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