Identifying a Data Source Using an Ad Hoc Name
New Information - SQL Server 2000 SP3.
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.
Important For each instance of SQL Server 2000, members of the sysadmin fixed server role can enable or disable the use of ad hoc connector names for an OLE DB provider using the SQL Server DisallowAdhocAccess property. When ad hoc access is enabled, any user logged on to that instance can execute SQL statements containing ad hoc connector names referencing any data source on the network that can be accessed using that OLE DB provider. To control access to data sources, members of the sysadmin role can disable ad hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. By default, ad hoc access is enabled for the SQL Server OLE DB provider, and disabled for all other OLE DB providers.
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',
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.