SQL Server Cross-server access data

-- 1. Use OpenRowSet directly
Create view name
As

Select * From OpenRowSet ('sqlodb', 'Another server name'; 'sa '; 'Password', database name. DBO, table name)

 

-- 2. Use the linked server

-- Create a connection server to access another server in the Enterprise Manager.

-- Or directly use the following statement to establish a connection server (srv_lnk is the name of the linked server, you can modify it on your own)
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqlodb', 'remote server name or IP address'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'username', 'Password'
Go

-- Then you can create a view.
Create view name
As

Select * From srv_lnk. Database Name. DBO, table name

 

Example:

Select .*
From OpenRowSet ('sqloledb', '192. 168.0.200 '; 'sa'; 'pass ',

'Select * from pubs. DBO. Authors order by au_lname, au_fname ') as

 

Complete.