SQL cross-server connection

 

1. log on to the SA or the master database with permissions. Execute the following statement in the query analyzer.

-- Establish a remote server connection

Exec sp_addrole server 'linkname', 'ms', 'sqloledb', 'serverip'

2. Continue execution

-- Create Remote Server login

Exec sp_add1_srvlogin 'linkname', 'false', null, 'uid', 'pwd'

3. query Test

-- Query a table

Select * From linkname. databasename. dbowner. tablename

-- Delete a connection
Exec sp_dropserver 'linkname', 'droplogins'

--- I have not tested the following
-- SQL2000 and later versions directly execute the remote stored procedure in the following ways:

Exec link_server_name.dbname.dbo.procname

-- Sql2005 and later versions can use exec () as linname to execute remote stored procedures:
Exec ('exec dbname. DBO. procname') at link_server_name;

-- The following statement deletes the connection record (if a connection is established but the connection is incorrect ).
-- Exec sp_dropserver ''linkname'', ''droplogins''