Use SQL-server for Distributed Query (link server ))

You can use the SQL-Server Enterprise Manager to create an instance. Pay attention to RPC and RPC out. You can also use SQL statements to complete the definition, which involves three stored procedures.
Sp_addmediaserver, sp_serveroption, and sp_addmediasrvlogin. the syntax of the three stored procedures is as follows:

Sp_addmediaserver
Create a linked server so that it can access distributed heterogeneous queries targeting the ole db data source. After using sp_addmediaserver to create a linked server, the server can perform distributed queries. If the linked server is defined as Microsoft? SQL Server ?, The remote stored procedure can be executed.
Syntax
Sp_addmediaserver [@ Server =] 'server'
[, [@ Srvproduct =] 'product _ name']
[, [@ Provider =] 'provider _ name']
[, [@ Datasrc =] 'data _ source']
[, [@ Location =] 'location']
[, [@ Provstr =] 'provider _ string']
[, [@ Catalog =] 'catalog ']
For example:

Sp_addrole server 'servername', '', 'sqloledb', 'dbconn', null, 'driver = {SQL Server}; server = 192.168.1.181; uid = SA, Pwd = ;'
Servername is the custom name, And dbconn is the defined ODBC data source.

Generally, the following parameters are used:
@ Server "linked server"
@ Srvproduct "product name"
@ Provider "provider"
@ Datasrc "Data Source"
Ii. Set server options
To use sp_serveroption, the following parameters are required:
@ Server "linked server"
@ Optname "RPC" or "RPC out"
@ Optvalue true or false
3. Add Login
To use sp_add1_srvlogin, the parameters are as follows:
@ Rmtsrvname "linked server"
@ Useself true or false
@ Locallogin local Login
@ Rmtuser: Remote User
@ Rmtpassword Remote User Password

Complete examples are as follows:

Exec sp_helpserver/* --- view the Remote Server LIST -------*/

Exec sp_helplinkedsrvlogin/* ------ view the logon user ---------*/

Exec sp_droplinkedsrvlogin 'sestlserver', null/* ---- Delete the logon user of the remote server -----*/

Exec sp_dropserver 'sestlserver', 'droplogins'/* ----- delete a remote server ------*/

Exec sp_addrole server 'sestlserver', '', 'sqlodb', 'sestlserver', null, 'driver = {SQL Server}; server = 192.168.0.2; uid = SA, Pwd = tianqibuhao ;'
Exec sp_serveroption n 'sestlserver', N 'rpc out', N 'true'
Exec sp_serveroption n 'sestlserver', N 'rpc ', N 'true'
Exec sp_add1_srvlogin 'sestlserver', 'false', null, 'sa ', 'tianqibuhao'
/* ------ Create a remote connection server ---------*/

/* --- Create a table on the remote server ----*/
Create Table tb2
(Student ID int, subject varchar (20), score INT)

Student ID, subject score
--------------------------------
6 politics 50
6 English 70
7. Language 80
7 mathematics 90
7. English 71

/* --- Create a table on the local server ----*/
Create Table tb1
(Student ID int, subject varchar (20), score INT)

Student ID, subject score
--------------------------------
1 Language 60
1 mathematics 70
1 English 75
1 politics 60
2 Language 55
2 mathematics 53
2 English 71
3. Language 80
4. Mathematics 0
5 English 91

/* --- Create a distribution view on the local server ----*/
Creat view student
Select * From tb1
Union all
Select * From sestlserver. Test. DBO. tb2

/* --- Query the distribution view ----*/
Select * from student

Student ID, subject score
--------------------------------
1 Language 60
1 mathematics 70
1 English 75
2 Language 55
2 mathematics 53
2 English 71
3. Language 80
4. Mathematics 0
5 English 91
1 politics 60
6 politics 50
6 English 70
7. Language 80
7 mathematics 90
7. English 71