Start Free Trial   Support

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';

See Also