OPENROWSET

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

Arguments

'provider_name' Is a character string that represents the friendly name (or PROGID) of the OLE DB provider as specified in the registry. provider_name has no default value.

'datasource' Is a string constant that corresponds to a particular OLE DB data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the IDBProperties interface of the provider to initialize the provider. Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands to locate the database or databases.

'user_id' Is a string constant that is the user name passed to the specified OLE DB provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider.user_id cannot be a Microsoft Windows login name.

'password' Is a string constant that is the user password to be passed to the OLE DB provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider. password cannot be a Microsoft Windows password.

'provider_string' Is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string typically encapsulates all the connection information required to initialize the provider.

BULK Uses the BULK rowset provider for OPENROWSET to read data from a file. In SQL Server, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

SELECT a.*

FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',

     'SELECT GroupName, Name, DepartmentID

      FROM AdventureWorks2012.HumanResources.Department

      ORDER BY GroupName, Name') AS a;