Stored Procedure operations across systems and databases

Step 1 create a remote connection

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 = ;'
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 ',

You can also define remote connection as follows:
Exec sp_addrole server 'dbx', '', 'sqloledb', '192. 168.0.1'

Exec sp_add1_srvlogin 'dbx', 'false', null, 'sa ',''

Run:
Select * From DBX. Database Name. DBO. Table Name

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/Flora_qxy/archive/2006/11/08/1372626.aspx