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