Accessing External Data
To access data from an OLE DB data source, you must provide SQL Server with the following information:
The name of the OLE DB provider that exposes the data source.
Any information the OLE DB provider must have to locate the source of the data.
Either the name of an object that the OLE DB data source can expose as a rowset, or a query that can be sent to the OLE DB provider that will cause the data source to expose a rowset. The objects that can be exposed as rowsets are known as remote tables. The queries that generate rowsets are known as pass-through queries.
Optionally, you can supply SQL Server with valid login IDs for the OLE DB data source.
SQL Server supports referencing heterogeneous OLE DB data sources in Transact-SQL statements by using: the linked server name or the ad hoc computer name.
Linked Server Names
A linked server is a virtual server that has been defined to SQL Server with all the information required to access an OLE DB data source. A linked server name is defined by using the sp_addlinkedserver system stored procedure. The linked server definition contains all the information required to locate the OLE DB data source. Local SQL Server logins are mapped to logins in the linked server by using sp_addlinkedsrvlogin.
Remote tables can be referenced by using the linked server name as one of the following:
The server name in a four-part name table or view reference in a Transact-SQL statement. The other three parts of the name reference an object in the linked server that is exposed as a rowset.
An input parameter to an OPENQUERY function. OPENQUERY sends the OLE DB provider a command to execute. The returned rowset can then be used as a table or view reference in a Transact-SQL statement.
For more information, see Identifying a Data Source by Using a Linked Server Name.
Ad Hoc Names
An ad hoc name is used for infrequent queries against OLE DB data sources that are not defined as linked servers. In SQL Server, the OPENROWSET and OPENDATASOURCE functions provide connection information for accessing data from OLE DB data sources.
By default, ad hoc names are not supported. The DisallowAdhocAccess provider option must be set to 0 and the Ad Hoc Distributed Queries advanced configuration option must be enabled.
.gif "Security note") Security Note |
|---|
Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider. SQL Server administrators should enable this feature only for highly trusted providers that are safe to be accessed by any local login. |
OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server. Neither OPENDATASOURCE nor OPENROWSET provide all the functionality of linked server definitions. This includes security management and the ability to query catalog information. Every time that these functions are called, all connection information, including passwords, must be provided.
OPENROWSET and OPENDATASOURCE appear to be functions and for convenience are referred to as functions; however, OPENROWSET and OPENDATASOURCE are macros and do not support supplying Transact-SQL variables as arguments.
OPENROWSET can be used with any OLE DB provider that returns a rowset, and can be used anywhere a table or view reference is used in a Transact-SQL statement. OPENROWSET is specified with the following:
All the information required to connect to the OLE DB data source.
Either the name of an object that will generate a rowset, or a query that will generate a rowset.
OPENDATASOURCE provides connection information as part of a four-part object name. This function supports only OLE DB providers that expose multiple rowsets by using the catalog**.schema.**object notation. OPENDATASOURCE can be used in the same locations in Transact-SQL syntax that a linked server name can be used. OPENDATASOURCE is specified with the following:
The name registered as the PROGID of the OLE DB provider that is used to access the data source.
A connection string that specifies the various connection properties to be passed to the OLE DB provider. The syntax of the connection string is a sequence of keyword-value pairs. The basic syntax is defined in the Microsoft Data Access Software Development Kit, and each provider documents the specific keyword-value pairs it supports. For more information about connection strings, see OPENDATASOURCE (Transact-SQL).
For more information, see Identifying a Data Source by Using an Ad Hoc Name.
Accessing Linked Servers
After a linked server is created by using sp_addlinkedserver, the server can be accessed by using the following:
Distributed queries that access tables in the linked server through SELECT, INSERT, UPDATE, and DELETE statements by using a linked server-based name.
Remote stored procedures executed against the linked server by using a four-part name.
An EXECUTE statement that takes an arbitrary parameterized pass-through command by using the AT linked_server_name extension. This command includes data definition language (DDL) and data manipulation language (DML) statements, or commands that return more than one result. For more information, see Using Pass-Through Queries As Tables.
sp_addserver is maintained as a compatibility feature for existing applications, but this stored procedure will not be supported in future releases. As applications are ported to SQL Server 2008, these applications may have to be run for a while with some new code that uses distributed queries against a linked server definition and some legacy code that uses a remote server definition. Both linked servers and remote servers use the same namespace. Therefore, either the linked server or the remote server definition must use a name that differs from the network name of the server being accessed remotely. Define one of the entries with a different server name, and use sp_setnetname to associate that definition with the network name of the remote server.