Using Sql-server for distributed queries (linked servers)

server| Distributed | server | Links can be built using Sql-server Enterprise Manager, note that RPC and RPC out two, or use SQL statements to complete the definition, mainly involving three stored procedures
Sp_addlinkedserver,sp_serveroption and sp_addlinkedsrvlogin, here are the syntax for three stored procedures:

sp_addlinkedserver
Create a linked server that allows access to distributed, heterogeneous queries against OLE DB data sources. After you use sp_addlinkedserver to create a linked server, the server can execute distributed queries. If the linked server is defined as Microsoft®sql Server™, remote stored procedures can be executed.
Grammar
sp_addlinkedserver [@server =] ' server '
[, [@srvproduct =] ' product_name ']
[, [@provider =] ' provider_name ']
[, [@datasrc =] ' data_source ']
[, [@location =] ' location ']
[, [@provstr =] ' provider_string ']
[, [@catalog =] ' catalog ']
For example:

sp_addlinkedserver ' ServerName ', ', ' SQLOLEDB ', ' dbconn ', null, ' Driver={sql server};server=192.168.1.181;uid=sa,pwd =;'
Here servername is the custom name, dbconn for the defined ODBC data source



The parameters commonly used are as follows:
@server "Linked server"
@srvproduct "Product name"
@provider "Provider"
@datasrc "Data Source"
Second, set server options
Using the sp_serveroption procedure, you want to use the following parameters:
@server "Linked server"
@optname "RPC" or "RPC out"
@optvalue True or False
Third, add login
Using the sp_addlinkedsrvlogin procedure, you want to use the following parameters:
@rmtsrvname "Linked server"
@useself True or False
@locallogin Local Login
@rmtuser Remote Users
@rmtpassword Remote user Password



All the complete examples are as follows:


EXEC sp_addlinkedserver ' 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_addlinkedsrvlogin ' test ', ' False ', null, ' sa ', '

Access can use the following statement: SELECT * from Test.pubs.dbo.tb_maintab

Above in the windows2000,sql-server2000 test pass