An ad hoc connector name can be used as a table reference when the OLE DB data source will not be referenced often enough to warrant configuring a linked server. Use the OPENROWSET function and specify all the information needed to access the OLE DB data source. The OPENROWSET function can be used in the place of a table or view name in a Transact-SQL statement.
OPENROWSET should be used only for accessing external data on an infrequent, ad hoc basis. It does not provide all of the functionality available from a linked server, such as security management and the ability to query the linked server catalog.
When using OPENROWSET, it is necessary to reissue all pertinent connection information for each query. The arguments of OPENROWSET supply the connection information and the object name used when accessing an OLE DB data source.
This example uses the ad hoc connector name to retrieve data from the Customers table of a Microsoft Access version of the Northwind sample database.
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'',
Customers)
| OPENROWSET | OPENQUERY |