Identifying a Data Source Using an Ad Hoc Name
An ad hoc 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. In Microsoft® SQL Server™ 2000, you can use the OPENROWSET and OPENDATASOURCE functions to provide an ad hoc name.
Both the OPENROWSET and OPENDATASOURCE functions provide ad hoc connection information. You can use these functions to specify all the information needed to access the OLE DB data source. However, you cannot use OPENROWSET and OPENDATASOURCE interchangeably.
You can use the OPENROWSET function wherever the OLE DB provider returns rowsets either by specifying a table (or view) name or by specifying a query that returns a rowset. The OPENROWSET function can be used in the place of a table or view name in a Transact-SQL statement.
--This example uses an ad hoc 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)
Use OPENDATASOURCE only when the provider exposes rowsets and uses the catalog.schema.object notation. This function can be used in the same Transact-SQL syntax locations a linked server name can be used. Thus, in the catalog.schema.object notation, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or a view name.
-- SELECT from a table on another instance of SQL Server.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
Both OPENROWSET and OPENDATASOURCE should be used only for accessing external data for ad hoc situations, when it is not possible to configure a permanent linked server. These functions do not provide all of the functionality available from a linked server, such as management of login mappings, ability to query the linked server's meta data, and the ability to configure various connection settings such as time-out values.
The arguments of OPENROWSET and OPENDATASOURCE do not support variables. They have to be specified as string-literal. If variables need to be passed in as arguments to these functions, a query string containing these variables can be constructed dynamically and executed using the EXEC statement.