Use OpenRowSet to connect to a remote SQL statement or insert data

Use OpenRowSet to connect to a remote SQL statement or insert data
-- For 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