sp_updatelinkedserver#

Updates a connection to a data source. A connection allows for access to a single data source. This stored procedure has no equivalent in SQL Server.

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

Syntax#

sp_updatelinkedserver [ @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 to 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 to the provider_string column in sys.servers view. Depending on the data provider, provider_string can hold information about pooling, data pushdown method, initializaiton 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.

Example#

The following example creates the myConnection, sets the datasrc to “mysql”, tests the connection, lists the updated connection using the sys.servers view, and drops the connection.

exec sp_addlinkedserver 'myConnection', 'MySql';

exec sp_updatelinkedserver 'myConnection', @datasrc='mysql:3306';

exec sp_addlinkedsrvlogin 'myConnection', 'FALSE', @rmtuser='root', @rmtpassword='root';
exec sp_testlinkedserver 'myConnection';
select * from sys.servers where name = 'myConnection';
exec sp_dropserver 'myConnection';