Linked servers allow SQL Server access to data from a remote data source. A remote data source can be another SQL Server instance or other data sources such MySQL, Access databases, Oracle, Excel workbooks, text files etc.
SQL Server connects to the remote data source via an OLE DB provider. There are variations of the OLE DB providers depending on data source that want to establish connection to. There are the Microsoft OLE DB Provider for Oracle, Microsoft OLE DB Provider for SQL Server, OLE DB Provider for Jet etc. providers:
Microsoft SQL Server supports connections to other OLE DB data sources on a persistent or an ad hoc basis. The persistent connection is known as a linked server. An ad hoc connection, that is made for the sake of a single query, is known as a distributed query.
A distributed query can be run without creating a linked server first, by using the Transact-SQL OPENROWSET and OPENDATASOURCE functions.
When executing a T-SQL statement against a linked server, the OLE DB provider (e.g. OLE DB Provider for Jet) for the linked server is loaded into the SQL Server memory address space, then SQL Server takes the T-SQL statement and calls the OLE DB interface. Then the OLE DB interface provides a connection to the remote data source (e.g. Excel file). The OLE DB provider then processes the result set and returns the results to the SQL Server client that made the initial call.
In this article we’ll show how to create a linked server to query Excel data from the Excel file (*.xlsx) using the Microsoft.ACE.OLEDB.12.0 OLE DB provider and also how can be done by using the OPENROWSET and OPENDATASOURCE functions.
Creating a linked server with the Microsoft.ACE.OLEDB.12.0 OLE DB provider
To import data from an Excel file (Excel 2007 (xlsx) and above) to SQL Server the Microsoft.ACE.OLEDB.12.0 OLE DB driver should be installed.
The Microsoft.ACE.OLEDB.12.0 OLE DB driver can be used on SQL Server 32-bit editions for Excel 2007 files, or later, or on SQL Server 64-bit editions for any Excel files.
There is one more provider; Microsoft. Jet. OLEDB.4.0 which can be used on SQL Server 32-bit editions for Excel 2003 files (or earlier).
A list of all currently available providers in SQL Server can be seen under the Providers folder:
From this link you can download and install the Microsoft.ACE.OLEDB.12.0 OLE DB driver. Depending on which version of SQL Server (32-bit or 64-bit) use, there are two versions of the Microsoft.ACE.OLEDB.12.0 OLE DB driver that can be installed:
- AccessDatabaseEngine.exe is for the SQL Server 32-bit version
- AccessDatabaseEngine_X64.exe is for the SQL Server 64-bit version
Note that if AccessDatabaseEngine_X64.exe was installed, and SQL Server 32-bit version is used. The error below may appear when creating a linked server for querying Excel data:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
– – – – – – – – – – – – – – – –
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
– – – – – – – – – – – – – – – –
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered. (Microsoft SQL Server, Error: 7403)
When the appropriate AccessDatabaseEngine executable file is installed, the Microsoft.ACE.OLEDB.12.0 OLE DB driver will appear in the list of the available drivers under the Providers folder:
After installing appropriate AccessDatabaseEngine, a linked server can be created. To do that, right click on the Linked Servers folder and choose the New Linked Server command:
This will open the New Linked Server dialog:
In this dialog, the name of a linked server and server type must be identified. In the Linked server box any name for the linked server can be entered (e.g. ExcelData).
If SQL Server is checked, then a linked server will be an SQL Server instance. More about creating a SQL Server linked server can be found on the How to create and configure a linked server in SQL Server Management Studio page. Since this article is about using the Excel data source, in the Server type section, choose the Other data source radio button and from the Providers combo box, choose the Microsoft Office 12.0 Access Database Engine OLE DB Provider item:
In the Product name box, put the name of the OLE DB data source to add as a linked server (e.g. Excel). In the Data source box the type the full path and file name of the Excel file (e.g. C:\Test\Excel_Data.xlsx).
For the Provider string field, enter Excel 12.0:
Note, if you are using the Excel 97-2003 (.xls) files, in the Provider string field should be enter “Excel 8.0”.
In case that the wrong name was entered in the Provider string field, the following error will appear:
The linked server has been created but failed a connection test. Do you want to keep the linked server?
– – – – – – – – – – – – – – – –
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
– – – – – – – – – – – – – – – –
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “EXCELDATA”.
OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “EXCELDATA” returned message “Could not find installable ISAM.”. (Microsoft SQL Server, Error: 7303)
After entering the correct name in the Provider string field, press the OK button on the New Linked Server dialog to create the new linked server. During the process of creating a linked server the below error message may appear:
Press Yes to create the EXCELDATA linked server. The EXCELDATA linked server will appear under the Linked Server folder:
But when you look for data in the Tables folder of the default database the following error will appear:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
– – – – – – – – – – – – – – – –
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
– – – – – – – – – – – – – – – –
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “EXCELDATA”. (Microsoft SQL Server, Error: 7303)
To resolve this error and the error above, close SQL Server Management Studio (SSMS) and run it again, but this time as an administrator:
Now, when expanding the Tables folder, the Excel sheets will appear. Note that every sheet is shown as SQL Server table with a dollar sign ($) at the end:
To create the a linked server by using T-SQL simply use the sp_addlinkedserver stored procedure paste and execute the following code into a query editor:
|
1 2 3 4 5 6 7 8 |
EXEC sp_addlinkedserver @server = N'EXCELDATA', @srvproduct = N'Excel', @provider = N'Microsoft.ACE.OLEDB.12.0', @datasrc = N'C:\Test\Excel_Data.xlsx', @provstr = N'Excel 12.0'; |
More about creating a linked server using T-SQL can be found on the How to create, configure and drop a SQL Server linked server using Transact-SQL page.
Querying Excel data using the linked server
Now that the linked server is created, let’s query some Excel data!
The following SQL code will list all data from the Sheet1 in the Excel_Data.xlsx file for which we are created the linked server:
|
1 2 3 |
SELECT * FROM EXCELDATA...[Sheet1$] |
But, when execute this code the error message may appear:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “EXCELDATA” reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 2
Cannot obtain the required interface (“IID_IDBCreateCommand”) from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “EXCELDATA”.
This usually happens because of inadequate permissions.
One way to resolve this is, go to the SQL Server Configuration Manager:
From the SQL Server Configuration Manager dialog, select SQL Server for on which has created a linked server:
Right click, and from the context menu, click the Properties option:
On the SQL Server Properties dialog under the Log on cart, choose the Built-in account radio button and from the combo box, select the Local System item:
From the SQL Server Properties dialog click the Apply button and press the Yes button on the Confirm Account Change warning message box:
Now, open SQL Server Manage Studio as administrator and in a query editor execute the SQL code:
|
1 2 3 |
SELECT * FROM EXCELDATA...Sheet1$ |
The following result will appear:
This will list all data from Sheet1 of the Excel_Data.xlsx file.
After all these settings, you are still getting the same error, maybe Microsoft Data Access Components (MDAC) do not be work properly. For more information, see this Microsoft article.
The following SQL code will insert data from Sheet1 to the SQL Server table ‘SQLTable’:
|
1 2 3 4 |
INSERT INTO dbo.SQLTable(ID,Name) SELECT ID, Name FROM EXCELDATA...Sheet1$ |
When query the SQLTable table, the following result will appear:
Querying Excel data using the distributed queries
To access data to an Excel file via SSMS without creating a linked server first use the Transact-SQL OPENROWSET and OPENDATASOURCE functions.
To establish connection and querying data from the Excel data source using OPENROWSET function type the following SQL code in query editor:
|
1 2 3 |
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test\Excel_Data.xlsx;', 'SELECT * FROM [Sheet1$]') |
But, when executing the above code the following error may occur:
Msg 15281, Level 16, State 1, Line 8
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
To resolve this error the Ad Hoc Distributed Queries option should be enabled in order to open a connection to a remote server using the OPENROWSET or OPENDATASOURCE. This can be achieved by using the sp_configure procedure and execute the following SQL code in a query editor:
|
1 2 3 4 5 |
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 |
If case that the above executed query show the following error:
Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.
Use the RECONFIGURE WITH OVERRIDE instead of RECONFIGURE:
|
1 2 3 4 5 6 |
EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE WITH OVERRIDE |
Now when executing the SQL code:
|
1 2 3 |
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test\Excel_Data.xlsx;', 'SELECT * FROM [Sheet1$]') |
The following result in the Results grid will be shown:
The same result can be obtained used the OPENDATASOURCE function. Type the following code:
|
1 2 3 4 |
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Test\Excel_Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$] |
Like linked servers, OPENROWSET and OPENDATASOURCE use OLE DB to connect to the remote data source; however, these functions do not encompass all the features included with linked servers, which is generally why linked servers are the preferred option.
Other articles in this series:
- How to create and configure a linked server in SQL Server Management Studio
- How to create, configure and drop a SQL Server linked server using Transact-SQL
- How to configure a Linked Server using the ODBC driver
- How to create a linked server to an Azure SQL database
See more
To boost your SQL Server development productivity, check out Free SQL Server Management Studio add-ins.
Useful links
- Querying remote data sources in SQL Server
- How to use Excel with SQL Server linked servers and distributed queries
- OPENDATASOURCE (Transact-SQL)
- OPENROWSET (Transact-SQL)
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019