sp_addlinkedserver

Creates a connection to a data source. A connection allows for access to a single data source (endpoint). After a new connection is created by using sp_addlinkedserver or GUI, a new virtual database can be created to run queries against the data source.

Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the remote server that connection points to.

Syntax

   sp_addlinkedserver [ @server= ] 'server' 
        [ , [ @srvproduct= ] 'product_name' ]
        [ , [ @datasrc=    ] 'data_source' ]   
        [ , [ @location=   ] 'location' ]   
        [ , [ @provstr=    ] 'provider_string' ]   
        [ , [ @catalog=    ] 'catalog' ]

Arguments

[ @server= ] 'server'

Is the name of the data source connection to create. server is sysname, with no default. It corresponds to the name column in sys.servers view.

[ @srvproduct= ] 'product_name'

Is the name of the data provider to add as a connection. product_name is nvarchar(128), with a default of NULL. It corresponds to the product column in sys.servers view.

[ @datasrc= ] 'data_source'

Is the network server or service name that hosts data source. data_source is nvarchar(4000). It corresponds with the data_source column in sys.servers view. Depending on the data provider, data_source can hold values in many formats like servername, servername:port, https://api.someserver.com/api/data, etc.

[ @location= ] 'location'

Is the extended configuration of the data connection in JSON format. location is nvarchar(4000), with a default of NULL. It corresponds to the location column in sys.servers view.

[ @provstr= ] 'provider_string'

Is the provider-specific configuration string in JSON format. provider_string is nvarchar(4000), with a default of NULL. It corresponds with the provider_string column in sys.servers view. Depending on the data provider, provider_string can hold information about pooling, data pushdown method, initialization sql, etc.

[ @catalog= ] 'catalog'

Is the catalog name at the source. Typically it is a database name for systems that support multiple databases hosted by the same instance. catalog is sysname, with a default of NULL;

Note: To learn the exact values to use for each of the parameters, first create the desired connection using GUI, and then examine the sys.servers view.

Examples

A. The following example creates a connection named mySqlServerConnection to the SQL Server database hosted by mysqlserver on default mssql port 1433, and sets the default database to AdventureWorksDW2012, and lists the newly created connection using the sys.servers view.

EXEC sp_addlinkedserver
    @server = 'mySqlServerConnection',
    @srvproduct = 'SQL Server',
    @datasrc = 'mysqlserver',
    @location = '{    "dialectVersion": "SQL Server",    "targetAgentInstance": null,    "targetLocalNetwork": null  }',
    @provstr = '{    "dataPushDownMethod": "TVP"  }',
    @catalog = 'AdventureWorksDW2012'
;

SELECT * 
  FROM sys.servers
 WHERE name = 'mySqlServerConnection';

B. The following example creates a connection named myOracleConnection to the Oracle database hosted by server myora on default Oracle port 1521, service name myora.mydomain.com, sets the connection method to EasyConnect, and lists the newly created connection using the sys.servers view.

EXEC sp_addlinkedserver
    @server = 'myOracleConnection',
    @srvproduct = 'Oracle',
    @datasrc  = 'myora:1521',
    @location = '{    "dialectVersion": "Oracle",    "targetAgentInstance": null,    "targetLocalNetwork": null  }',
    @provstr  = '{    "connectionMethod": "Easy Connect",    "serviceName": "myora.mydomain.com",    "pooling": "true",    "minPoolSize": "1",    "maxPoolSize": "111",    "incrPoolSize": "1",    "decrPoolSize": "1",    "fetchSize": "4096",    "dataPushDownMethod": "None"  }',
;


SELECT * 
  FROM sys.servers
 WHERE name = 'myOracleConnection';

See Also