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', 'Data source = servername; user id = myuid; Password = mypass', database name. DBO. Table name)
-- Import example
Select * into table from OpenRowSet ('sqloledb', 'Data source = servername; user id = myuid; Password = mypass', 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, which is provided by ole db for jet.ProgramQuery an Excel worksheet.
Select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: financeaccount.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions
If you use OpenDataSource/OpenRowSet only occasionally, the linked server is frequently used.