Cross-server access to SQL database

Use OpenRowSet to connect to a remote SQL statement or insert data. If you only need temporary access, you can directly use OpenRowSet
-- Query example
Select * From OpenRowSet ('sqloledb'
, 'SQL Server name'; 'username'; 'Password'
, Database name. DBO. Table name)


-- Import example
Select * into table from OpenRowSet ('sqloledb'
, 'SQL Server name'; 'username'; 'Password'
, Database name. DBO. Table name)

-- Create a linked server
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqlodb', 'remote server name or IP address'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'username', 'Password'
Go

-- Query example
Select * From srv_lnk. Database Name. DBO. Table Name

-- Import example
Select * into table from srv_lnk. Database Name. DBO. Table Name

-- Delete the linked server when it is no longer in use
Exec sp_dropserver 'srv _ lnk ', 'droplogins'
Go

-- The following example accesses data from a table, which is in another instance of SQL Server.

Select * From OpenDataSource ('sqloledb', 'Data source = servername; user id = myuid; Password = mypass'). northwind. DBO. Categories

The following is an example of a query. It queries an Excel spreadsheet through the ole db provider for jet.

Select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C:/finance/account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions