You could often run AD hoc to query and extract data from remote OLE DB data source, or import the data in batches into the table of SQL Server 2008.
In this case, to you could use OPENROEWSET function to a connection string into the data source.
Or, with the function OPENROEWSET, you could also access the data from any data source that support the registration of OLE DB, for example: to access data from the remote instance in SQL Server or Access. But from the instance in SQL Server 2008, the remote instance must allow the configuration of ad hoc distributed query .
To make remote SQL Server instance support ad hoc query, there is a need to use sp_configure to set “advanced options”, and then start Ad hoc Distributed Queries .Let’s look at T-SQL script below:
EXEC sp_configure ’show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE;
GO
It’s important to note that you must run the command “RECONFIGURE” after completing the stored process of sp_configure.
As long as you have completed the above configuration, you can use the function OPENROWSET, which could be used in the FROM clause of SELECT statement. The following example will show the basic syntax of the function:
OPENROWSET(’provider’, ‘connection string’, target)
There are three parameters in the function:
1. Provider: ProgID .
2. connection string –It is relevant with the provider .
3. Target : it can be a database object or a query.
The following example will show the usage of 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
It needs to be emphasized; we used OPENROWSET function and its three-parameter
The first parameter is the name of the SQL Server OLE DB Provider.
The second parameter is connection string. For SQL Server provider, all of the string should be enclosed in quotation marks, in which each group of information should be separated by semicolon .In the above instance, the first group of information specified the target server of SQLSRV1, and the second designated the trusted part of the connection.
When the Server was designated, if the instance was not the default one of the Server 2008, you should designate the instance name in the connection string.(the provider of SQLNCLT also supports other parameters.)
The last parameter is the select statement that actually is to execute the function.
It should be noted it is to use object names in SQL statement to access the view .
Then we could use OPENROWSET function.
What is the above-mentioned is we could extract the data from the data source, not from SQL Server.
For example: we can query the employees table of the Microsoft Access database with the following Select statement:
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
Also, you could query data from multiple data sources with OPENOWSET function.
For example, the following example can show that you can use inner join to query data from the remote instance of SQL Server and 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
Now let’s look at the other important function in OPENROWSET, that is to input the data in batches .To be better understood, I shall create the table Employees and import data in AdventureWorks database with the script below:
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
It is important to note that, I haven’t imported the data of ResumeFile column, whose type is VARBINARY (MAX).But I will import the file Employee1.docx as binary data into the column with Update statement.
USE AdventureWorks
GO
UPDATE Employees
SET ResumeFile = (
SELECT *
FROM OPENROWSET(BULK ‘C:\Data\Employee1.docx’, SINGLE_BLOB)
AS ResumeContent)
WHERE EmployeeID = 1
From the above examples, we can get that the OPENROWSET function can provider the option BULK, with which you can import the data. However, you need to designate the file you want to import the data into when you use the option BULK.