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_configure system stored procedure to first set the advanced options and then permit the ad hoc distributed queries, as shown in the following T-SQL script:<...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
/p>
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.
Now let's look at one other important function of OPENROWSET -- bulk load. To demonstrate how this works, I used the following script to create and populate the Employees table in the AdventureWorks database:
USE AdventureWorks
GO
IF OBJECT_ID (N'Employees', N'U') IS NOT NULL
DROP TABLE dbo.Employees
GO
SELECT EmployeeID, FirstName, LastName, JobTitle
INTO Employees
FROM HumanResources.vEmployee
GO
ALTER TABLE Employees
ADD ResumeFile VARBINARY(MAX) NULL
GO
Notice that I did not populate the ResumeFile column, which is configured with the VARBINARY(MAX) data type. Instead, I can use the following UPDATE statement to bulk load the Employee1.docx file as binary data into the column:
USE AdventureWorks
GO
UPDATE Employees
SET ResumeFile = (
SELECT *
FROM OPENROWSET(BULK 'C:\Data\Employee1.docx', SINGLE_BLOB)
AS ResumeContent)
WHERE EmployeeID = 1
As you can see, the OPENROWSET function supports the BULK option, which you must use when bulk loading data. To use this option, specify the file that you want to load and how to load it. Since I want to store the file as a binary, I included the SINGLE_BLOB option in the example above. I could have instead specified that the data be stored as character data by using the SINGLE_CLOB or SINGLE_NCLOB options (if the column supported character data). In addition, you can use a format file when calling the OPENROWSET function to bulk load data, but a discussion about format files is beyond the scope of this article.
Whether you use OPENROWSET to bulk load data or to connect to an OLE DB data source, you'll find it's a handy tool for retrieving data. For more details about the function, be sure to check out the "OPENROWSET (Transact-SQL)" topic in SQL Server Books Online. There you can also find details about the SQLNCLI provider (or the SQLNCLI10 provider in SQL Server 2008).
ABOUT THE AUTHOR:
[IMAGE]
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. His books include Beginning MySQL (part of the Wrox Programmer-to-Programmer series), SQL: A Beginner's Guide (based on the SQL:1999 standard), MCSE Training Kit: Designing Highly Available Web Solutions with Microsoft Windows 2000 Server Technologies, and MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. You can find more information at http://www.rhsheldon.com.
