Connection Establishment and Property Retrieval

SQL Server supports two remote data object naming conventions: linked server-based four-part names and ad hoc names using the OPENROWSET function.

Linked server-based names

A linked server serves as an abstraction to an OLE DB data source. A linked server–based name is a four-part name of the form <linked-server>.<catalog>. <schema>.<object>, where <linked-server> is the name of the linked server. SQL Server interprets <linked-server> to derive the OLE DB provider and the connection attributes that identify the data source to the provider. The other three-name parts are interpreted by the OLE DB data source to identify the specific remote table.

Ad hoc names

An ad hoc name is a name based on the OPENROWSET function. It includes all the connection information (that is, the OLE DB provider to use, the attributes needed to identify the data source, the user ID and password) every time the remote table is referenced in a distributed query.

If a linked server name is used, SQL Server extracts from the linked server definition the OLE DB provider name and the initialization properties for the provider. If an ad hoc name is used, SQL Server extracts the same information from the arguments of the OPENROWSET function.

For detailed instructions about setting up a linked server using a four-part name and ad hoc name–based syntax, see SQL Server Books Online.