Its provides the connection to external data objects or data source, using linked server you can extract the data from the different data source for a example oracle, ms access and other databases. you can execute DML operation to other sql server provided you should have permissions to do the same. Providers acts as bridge between two different instance (for example : sql server and oracle)
Linked server can be created by using T-SQL script also same can be done using SSMS (SQL Server Management Studio)
EXEC sp_addlinkedserver @server = ‘Demo’,
@srvproduct = ‘Microsoft.Jet.OLEDB.4.0’,
@provider = ‘Microsoft.Jet.OLEDB.4.0’,
@datasrc = ‘C:\Northwind.mdb’
How can you create linked server using SSMS ?
Follow the below steps to create linked server.
Connect to server using SSMS ->Click on server object ->Right click on Linked Server
1. Give the Server name
2. Select the provider
3. Product name
4. Database name or file path in case of ms access, Click on Security option.
Select the option (Be made using this security context and give the remote user name and password)
5. Click on OK
Linked Server is ready now you can see the database objects from another sql instance, under linked server node.
Execute the below query to check whether you can access data from different sql server instance using linked server.
select * from [NUNQO38SVH\SQL2005].DBA.DBO.st_details
You can also execute the DML statement against table of the other instance.
Insert into [NUNQO38SVH\SQL2005].DBA.DBO.st_details
Delete from [NUNQO38SVH\SQL2005].DBA.DBO.st_details