-- Create a linked server
exec sp_addlinkedserver ' Linked server name ', '', 'SQLOLEDB', ' Remote server name or ip address '
exec sp_addlinkedsrvlogin ' Linked server name ', 'false' ,null, ' You need to link to the server username ', ' password '
sp_addlinkedserver
[ @server= ] 'server'
[ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
parameter (Parameters):
[ @server = ] 'server'
The name of the linked server to create. server Is the data type sysname , there is no default value.
[ @srvproduct = ] 'product_name'
To add as a linked server OLE DB The product name of the data source. product_name Is the data type nvarchar(128) , the default value is NULL . If it is SQL Server , do not specify provider_name , data_source , location , provider_string and catalog .
[ @provider = ] 'provider_name'
Corresponding to this data source OLE DB Access only to the interface 1 Programming identifier (PROGID) . Specifies the installation on the current computer OLE DB Access interface, provider_name Must be only 1 . provider_name Is the data type nvarchar(128) , the default value is NULL ; But if you ignore provider_name , use the SQLNCLI . SQLNCLI is SQL This machine OLE DB Access interface. OLE DB The access interface should be specified PROGID Register in the registry.
[ @datasrc = ] 'data_source'
by OLE DB Access the name of the data source interpreted by the interface. data_source Is the data type nvarchar(4000) . data_source As a DBPROP_INIT_DATASOURCE Property is passed to initialize OLE DB Access interface.
[ @location = ] 'location'
by OLE DB Access the location of the database interpreted by the interface. location Is the data type nvarchar(4000) , the default value is NULL . location As a DBPROP_INIT_LOCATION Property is passed to initialize OLE DB Access interface.
[ @provstr = ] 'provider_string'
OLE DB Access an interface specific connection string that identifies only 1 Data source. provider_string Is the data type nvarchar(4000) , the default value is NULL . provstr Or passed to IDataInitialize Or is set to DBPROP_INIT_PROVIDERSTRING Property to initialize OLE DB Access interface.
On the SQL Native client OLE DB After the access interface is created, the linked server can be SERVER Keywords used for SERVER=servername/instancename To specify the instance to specify the specific SQL Server Instance. servername Is to run SQL Server The name of the computer, instancename Is the specific to which the user will connect SQL Server The name of the instance.
[ @catalog = ] 'catalog'
with OLE DB The directory used by the access interface to establish the connection. catalog Is the data type sysname , the default value is NULL . catalog As a DBPROP_INIT_CATALOG Property is passed to initialize OLE DB Access interface. On the SQL Server When the instance defines a linked server, the directory points to the default database that the linked server is mapped to.
Return code value (returnValue):
(success) or 1 (failure)
permissions :
requires ALTER ANY LINKED SERVER Permissions.
---------------------------------------
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' [ , [ @useself = ] 'useself' ] [ , [ @locallogin = ] 'locallogin' ] [ , [ @rmtuser = ] 'rmtuser' ] [ , [ @rmtpassword = ] 'rmtpassword' ] Pay attention to : Cannot be executed from a user-defined transaction sp_addlinkedsrvlogin . parameter (Parameters):[ @rmtsrvname = ] 'rmtsrvname' The name of the linked server to which the login mapping is applied. rmtsrvname Is the data type sysname , there is no default value. [ @useself = ] 'useself' Determine the login name to use to connect to the remote server. useself Is the data type varchar(8) , the default value is TRUE . A value of true Specifies that the login USES its own credential connection rmtsrvname To ignore rmtuser and rmtpassword Parameters. false Specify the use rmtuser and rmtpassword Parameter connection specification locallogin the rmtsrvname . if rmtuser and rmtpassword Also set to NULL , you do not use a login or password to connect to the linked server. [ @locallogin = ] 'locallogin' Login on the local server. locallogin Is the data type sysname , the default value is NULL . NULL Specifies that this applies to the connection to rmtsrvname All local logins. If not for NULL , locallogin Can be SQL Server Log in or Windows To log in. for Windows To log in, you must do so either directly or through authorized access Windows Group membership grants access SQL Server The permissions. [ @rmtuser = ] 'rmtuser' when useself for false Is used for connection rmtsrvname User name. rmtuser Is the data type sysname , the default value is NULL . [ @rmtpassword = ] 'rmtpassword' with rmtuser The associated password. rmtpassword Is the data type sysname , the default value is NULL . Return code value (returnValue): 0 (success) or 1 (failure) permissions : You need to have it on the server ALTER ANY LOGIN Permissions.
----------------------------------------------- ----------------------------------------------- Query examples select * from Linked server name . The database name .dbo. The name of the table