SQL remote Connection

I. Adding a remote connection
EXEC sp_addlinkedserver @server = ' 254 ', @srvproduct = ',--the product name of the OLE DB data source of the linked server
@provider = ' SQLOLEDB ', @datasrc = ' 192.168.1.254 ';

[ @server = ] ' Server '
The name of the linked server to be created. The server has a data type of sysnameand no default value.

[ @srvproduct = ] ' Product_Name '
The product name of the OLE DB data source to be added as a linked server. The library is nvarchar,and the default value is NULL. If SQL Server, provider_name, data_source, location , provider_string , and the directory does not need to be specified.

[ @provider = ] ' provider_name '
The unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. provider_name must be unique on the current computer to install the specified OLE DB provider. provider_name is nvarchar, thedefault value is NULL; However, if provider_name is omitted, then SQLNCLI is used. (using SQLNCLI and SQL Server is redirected to the latest version of the SQL Server Native Client OLE DB provider.) The OLE DB provider should be registered in the registry with the specified PROGID.

[ @datasrc = ] ' Data_source '
The name of the data source interpreted by the OLE DB provider. data_source is nvarchar (4000). Data_source is passed as the Dbprop_init_datasource property of the OLE DB provider to initialize.

[ @location = ] ' Location '
The location of the database interpreted by the OLE DB provider. The location is nvarchar (4000)and the default value is NULL. The location is passed as the Dbprop_init_location property of the OLE DB provider to initialize.

[ @provstr = ] ' provider_string '
An OLE DB provider-specific connection string that identifies a unique data source. provider_string is nvarchar (4000)and the default value is NULL. Provstr is passed to IDataInitialize, or is set to the Dbprop_init_providerstring property to initialize the OLE DB provider.
two. Add a remote Login
EXEC sp_addlinkedsrvlogin
' 254 ',--the server alias being accessed
' False ',
Null
' Dev ',--account number
' hello123 ';--password
GO

[@rmtsrvname = ] ' rmtsrvname '
Name of the linked server to which the login mapping is applied. rmtsrvname is sysname, no default value.

[@useself = ] ' TRUE' | ' FALSE ' | ' NULL '
Determines whether to connect to rmtsrvname by impersonating a local login or explicitly submitting a login and password. The data type is varchar (8)and the default value is TRUE.

A value of TRUE specifies that the login name uses its own credentials to connect to the rmtsrvname, with rmtuser and rmtpassword arguments ignored. FALSE Specifies that the rmtuser and rmtpassword parameters are used to connect to the rmtsrvname specified locallogin. If rmtuser and Rmtpassword are also set to NULL, any logins or passwords are used to connect to the linked server.

[@locallogin = ] ' LocalLogin '
Login on the local server. locallogin is sysnameand the default value is NULL. NULL Specifies that this entry applies to all connections to the local login name rmtsrvname. If not NULL, LocalLogin can be either a SQL Server login or a Windows logon name. For Windows logins, they must be granted access to SQL Server either directly or through Windows group memberships that have been granted access.

[@rmtuser = ] ' Rmtuser '
is used to connect to the Telnet name rmtsrvname when @useself is FALSE. instance of the remote server when SQL Server does not use Windows authentication, rmtuser is the SQL Server logon name. rmtuser is sysnameand the default value is NULL.

[@rmtpassword = ] ' Rmtpassword '
Password associated rmtuser. rmtpassword is sysname, default value is NULL

Three. OpenQuery remote function Reference and parameter passing value
SELECT * from OPENQUERY ([254], ' SELECT * from agency0901.[ Dbo].func_jijiawebsync_getpropertylist ("', ' 2017-5-24 ', 0) ');

SQL remote Connection