Using the OPENROWSET function in SQL Server

Whether you're bulk loading data or connecting to an OLE DB data source, OPENROWSET is a handy tool for retrieving data. Find out how to use the OPENROWSET function for SQL Server and Microsoft Access.

There may be times when you'll want to run an ad hoc query that retrieves data from a remote OLE DB data source...

or bulk loads data into a SQL Server table. In such cases, you can use the OPENROWSET function in Transact-SQL to pass a connection string and query to the data source in order to retrieve the necessary data.

You can use the OPENROWSET function to retrieve data from any data sources that support a registered OLD DB provider, such as a remote instance of SQL Server or Microsoft Access. If you're using OPENROWSET to retrieve data from a SQL Server instance, that instance must be configured to permit ad hoc distributed queries.

To configure the remote instance of SQL Server to support ad hoc queries, use the sp_configuresystem stored procedure to first set the advanced options and then permit the ad hoc distributed queries, as shown in the following T-SQL script:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE;
GO

Note that you must run the RECONFIGURE command after you run the stored procedure.

Once you've configured the remote SQL Server instance, you can use the OPENROWSET function against that instance. The function should be included in the FROM clause of your SELECT statement. The following syntax shows the function's basic elements:

OPENROWSET('provider', 'connection string', target)

As you can see, the function takes three arguments:

• Provider -- The friendly name (ProgID) of the OLE DB provider supported by a particular data source, as defined in the registry. The provider name must be enclosed in single quotes.
• Connection string -- A provider-specific string that includes the details necessary to connect to the data source specified within the string. The connection string is enclosed in one or more sets of single quotes, depending on the provider.
• Target -- The target can be either a schema object or a query:
  • Object -- The name of a schema object, such as a table or view. The object name should be qualified as necessary, though the name should not be enclosed in single quotes.
  • Query -- The SELECT statement that retrieves data from the remote data source. The query must be enclosed in single quotes.

The example below demonstrates how to use the OPENROWSET function:

SELECT Employees.*
FROM OPENROWSET(
'SQLNCLI',
'Server=SqlSrv1;Trusted_Connection=yes',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM AdventureWorks.HumanResources.vEmployee
ORDER BY LastName, FirstName'
) AS Employees

Notice that the SELECT statement's FROM clause contains the OPENROWSET function and its three arguments. The first argument (SQLNCLI) is the name of the SQL Server OLE DB provider.

The second argument is the connection string. For a SQL Server provider, the entire connection string should be enclosed in one set of single quotes, with each element in the connection string separated by a semicolon. In the example above, the first element identifies the target server (SqlSrv1), and the second element specifies that a trusted connection should be used. When specifying the target server, be sure to include the instance name, as the server name of the instance is not the default. (Note that the SQLNCLI provider also supports additional arguments.)

The final argument in the OPENROWSET function is the actual SELECT statement. Notice that the statement uses the fully qualified name when accessing the view.

That's all there is to using the OPENROWSET function. The function returns a dataset (which I've named "Employees"), and that dataset can be treated like any other result returned by the FROM clause.

As mentioned above, you can also retrieve data from sources other than SQL Server. For instance, the following SELECT statement queries the Employees table in a Microsoft Access database:

SELECT Employees.*
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Data\Employees.mdb';'admin';' ',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM Employees
ORDER BY LastName, FirstName'
) AS Employees

You might notice that the provider is different from the one used for SQL Server. In this case, the provider is Microsoft.Jet.OLEDB.4.0. (Note that a new provider is also available for Access 2007.)

The connection string is also different from the preceding example. To begin with, the connection string is separated into three parts, each enclosed in its own set of single quotes, and the parts are separated with semicolons.

The first part identifies the path and file name of the Access database file. This is followed by the admin user account -- an administrative account built into Access. The third part is an empty string, which is the password. Because no password has been defined for the admin account, an empty string is used. If the account were configured with a password, it would go here.

The entire connection string is followed by a comma and then the SELECT statement used to retrieve data from the Access database. (I created the Employees table by importing the vEmployee view from SQL Server.)

That's all there is to returning data from Access. Your query will now return a result set similar to what you would see if you were accessing a local SQL Server database.

You can also use the OPENROWSET function to retrieve data from multiple data sources. For instance, in the following example, I use an inner join to join data from a remote instance of SQL Server and an Access database:

SELECT e1.EmployeeID, e2.FirstName, e2.LastName, e1.JobTitle
FROM OPENROWSET(
'SQLNCLI',
'Server=SqlSrv1;Trusted_Connection=yes;',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM AdventureWorks.HumanResources.vEmployee'
) AS e1
INNER JOIN OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Data\Employees.mdb'; 'admin';' ',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM Employees'
) AS e2
ON e1.EmployeeID = e2.EmployeeID
ORDER BY e2.LastName, e2.FirstName

Notice that the outer SELECT statement returns data from both tables -- the employee ID and job title from SQL Server along with the first and last names from Access. As long as you can form a credible join, you can treat the data as though you are joining tables from the local instance of SQL Server.