Use SQL server for Distributed Query

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, a 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_addrole server 'test', '', 'sqloledb', 'test', null, 'driver = {SQL server}; server = ipaddress; uid = sa, pwd = ;'
Exec sp_serveroption N 'test', N 'rpc out', N 'true'
Exec sp_serveroption N 'test', N 'rpc ', N 'true'
Exec sp_add1_srvlogin 'test', 'false', null, 'sa ',''

The following statement can be used for access: select * from test. pubs. dbo. tb_maintab

Above in windows2000, sql-server2000 test passed