SQL server database server Link

Source: Internet
Author: User
Cause: There are many users and a large amount of data. Multiple databases occupy one database server. Linked Server is used to process distributed queries: when the client application executes distributed queries through the linked Server, SQL Server analyzes the commands and sends requests to OLE DB.

Sp_addmediaserver: register the remote server information on the local server.

Sp_add1_srvlogin: create or update the sp_ing between the login name on the local SQL Server instance and the security account in the remote server sp_addmediaserver [@ server =] 'server' // name of the connection Server to be created [, [@ srvproduct =] 'product _ name'] // product name of the OLEDB data source linked to the Server. The default value is NULL. If it is SQL Server, you do not need to specify it. [, [@ Provider =] 'provider _ name'] // unique programming identifier of the ole db access interface corresponding to this data source

[, [@ Datasrc =] 'data _ source'] // name of the data source interpreted by the OLEDB access interface

Sp_add1_srvlogin

[@ Rmtsrvname =] 'rmtsrvname' // name of the linked server mapped to application Logon

[, [@ Useself =] 'true' | 'false' | NULL] // determines whether to connect to rmtsrvname by simulating a local login name or explicitly submitting a login name and password. The default value is TRUE.

If the value is True, the login name uses its own creden to connect to the rmtsrvname, while ignoring the rmtuser and rmtpassword parameters;

The value of False indicates that the rmtuser and rmtpassword parameters are used to connect to the rmtsrvname of the specified locallogin. If the rmtuser and rmtpassword are also set to NULL, the login name or password is not used to connect to the server.

[, [@ Locallogin =] 'locallogin'] // logon on the local server. The default value is null. Null indicates that this option is applied to all local logins connected to the rmtsrvname. If it is not null, locallogin can be an SQL Server logon or Windows logon.

[, [@ Rmtuser =] 'rmtuser'] // The remote login name used to connect to the rmtsrvname when @ useself is FALSE. The default value is NULL.

[, [@ Rmtpassword =] 'rmtpassword'] // password associated with rmtuser. The default value is NULL.

 

Example:

Exec sp_addrole server 'pub', '', 'sqloledb', '2017. 168.1.38 'exec sp_addinto srvlogin 'pub', 'false', null, 'sa ', '000000' SELECT * FROM pub. ce_pub.dbo.t_pub_user
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.