Create Linked Server Using SQL Server Management Studio

In this article I demonstrate how to connect the two different databases with the help of Linked Server.
  • 2755

In this article I demonstrate how to connect the two different databases with the help of Linked Server. Linked Server allows you to connect to other database instances on the same server or on another machine or remote servers. Other data source could be SQL Server instance or it could be Oracle, MS Access, DB2, or a MS Excel file. 

To view information about all Linked Server, execute following code:

sp_linkservers.jpg

By using SQL Server Management Studio, we can create Linked Servers.

Step 1

Open SSMS. Go to ServerObjects -> Linked Server - > Create new Linked Server.

newLinkedServer.jpg

New window for setup as below will open.

newLinkedServer1.jpg

In New Window,

  • Linked Server is name used for linked server.
  • Server Type can be SQL Server or other data sources.
  • Provider is the identifier for OLE DB provider. If this parameter is not specified then default value of provider_name is SQL Server data source. Provider_name for SQL Server is SQLNCLI (for Microsoft SQL Native Client OLE DB Provider). MSDAORA is used for Oracle, OraOLEDB.Oracle for Oracle versions 8 and higher, Microsoft.Jet.OLEDB.4.0 for MS Access and MS Excel, DB2OLEDB for IBM DB2, and MSDASQL for an ODBC data source.
  • product_name is the name of OLEDB data source e.g. Oracle, SQL Server.
  • Data source  is the data source e.g. Oracle Database data source or other provider data source.
  • provider_string specifies the connection string specific to the OLE DB provider. It is an optional parameter.
  • Catalog is database name. It is an optional parameter.
     

Step 2

Enter Linked Server name and provide Data Source information.

newLinkedServer2.jpg

Step 3

Provide login credential details also under the Security tab at the top left corner.

newLinkedServer5.jpg

Step 4

In Server Options tab we can update Connection Timeout, query execution timeout etc. Click OK. You can view created linked server from here.

Result:

 newLinkedServer4.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.