Create Linked Server Using T-SQL
In this article I demonstrate how to connect the two different databases with the help of Linked Server.
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.
Syntax
sp_addlinkedserver
[@server=]'server'[,
[@srvproduct=]'product_name']
[,[@provider=]'provider_name']
[,[@datasrc=]'data_source']
[,[@location=]'location']
[,[@provstr=]'provider_string']
[,[@catalog=]'catalog']
Here,
Server is name used for linked server.
product_name is the name of OLEDB data source.
provider_name 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.
data_source is the data source.
Location is the file location as interpreted by the given OLE DB provider.
provider_string specifies the connection string specific to the OLE DB provider.
To view information about all Linked Server, execute following code:
Example
Now in this example I will demonstrate how to create linked server connection to another SQL Server instance.
EXEC sp_addlinkedserver @server= N'ADDSERVER',
@srvproduct= N'SQL Server'
Result:
