The .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. To import data from an Excel file (Excel 2007 (xlsx) and above) to SQL Server the .12.0 OLE DB driver should be installed. In this article we’ll show how to create a linked server to query Excel data from the Excel file (*.xlsx) using the .12.0 OLE DB provider and also how can be done by using the OPENROWSET and OPENDATASOURCE functions.Ĭreating a linked server with the .12.0 OLE DB provider The OLE DB provider then processes the result set and returns the results to the SQL Server client that made the initial call. Then the OLE DB interface provides a connection to the remote data source (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. When executing a T-SQL statement against a linked server, the OLE DB provider (e.g. An ad hoc connection, that is made for the sake of a single query, is known as a distributed query.Ī distributed query can be run without creating a linked server first, by using the Transact-SQL OPENROWSET and OPENDATASOURCE functions. The persistent connection is known as a linked server. Microsoft SQL Server supports connections to other OLE DB data sources on a persistent or an ad hoc basis. There are the Microsoft OLE DB Provider for Oracle, Microsoft OLE DB Provider for SQL Server, OLE DB Provider for Jet etc. There are variations of the OLE DB providers depending on data source that want to establish connection to.

SQL Server connects to the remote data source via an OLE DB provider. A remote data source can be another SQL Server instance or other data sources such MySQL, Access databases, Oracle, Excel workbooks, text files etc. Linked servers allow SQL Server access to data from a remote data source.

Access 2016 installable isam