很多同学在使用 TSQL 查询 Excel 数据的时候，总是遇到各种问题不知如何处理。今天，这篇文章就谈谈 SQL Server 中两个最常用的 OLEDB 驱动程序 —— Microsoft.ACE.OLEDB.12.0 和 Microsoft.Jet.OLEDB.4.0 。只要安装至服务器上，你就可以通过 OPENROWSET 命令从 Excel 电子表格和文本文件中插入、查阅、更新和删除数据。

如何检查安装了哪些 OLEDB 提供程序

要检查你安装了哪些提供程序，可以运行以下查询：

EXEC master.dbo.sp_MSset_oledb_prop

sqlserver驱动包maven sql server驱动程序_microsoft

或者通过 SSMS 查看：

sqlserver驱动包maven sql server驱动程序_sqlserver驱动包maven_02

ACE OLEDB 和 Jet OLEDB 的区别

它们非常相似。对于 INSERT、UPDATE、DELETE 和 SELECT 等基本操作，你几乎不会注意到这两个 OLEDB 驱动程序之间的任何区别，因为它们具有相同的参数并且以完全相同的方式工作。但是他们之间有什么变化呢？为什么是两个驱动？

JET 驱动程序于 1992 年发布，长期以来满足了许多开发人员的需求，以简单实用的方式提供不同数据源之间的集成，抽象了技术问题。随着 Windows 在 x64（64 位）平台上的出现，JET 开始不再为开发者服务，因为驱动程序只在 x86（32 位）平台上有本机支持，并且能够访问 MDB 数据库和其他数据源，有必要使用 32 位软件作为代理。

意识到这种情况，Microsoft 发布了 Office 2007 以及随之而来的新版 JET，现在称为 Office Access Connectivity Engine (ACE)，它允许与 JET 4.0 及其以前的版本兼容，并支持新的 Access 格式 (. accdb)，它为 Access 带来了多项新功能，例如多值字段、安全性和加密改进。尽管如此，ACE 并没有保留 JET 4.0 版的一些重要功能，例如复制功能和用户级安全性。

在 Access 2010 中，ACE 驱动程序获得了对 64 位平台的支持，其核心被认为是 JET 驱动程序的 64 位版本。

分析这 2 个驱动程序，我们发现它们对于简单的操作非常相似，但是当我们涉及 union、join、nested 等查询时，结果很可能会不一样。ACE 驱动程序对 JET 等旧文件没有如此强大的支持。如果你在旧版本的 Access 中打开这些文件，例如，当你在 TEXT 类型字段上执行 UNION 时，JET 将返回 TEXT ( 255)，ACE 返回 MEMO。

因此，如果你使用的是 32 位操作系统（目前不推荐），你可以在 ACE 和 JET 之间进行选择。如果你使用的是 64 位版本，则只能使用 ACE。总的来说，建议使用使用ACE。

安装 Microsoft Jet OLEDB 驱动程序

正如上面已经提到的，JET OLEDB 驱动程序在 64 位环境中不起作用。所以我们不得不创建另一个 32 位 VM 来执行安装来试验。

sqlserver驱动包maven sql server驱动程序_提供程序_03

如你所见，当我们的系统为 Windows Server 2008 R2 x86 和 SQL Server 2012，则无需安装任何东西即可使用 JET 提供程序。查看 Excel 内容如下：

EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
GO
-- OPENROWSET
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\Pasta1.xls', [Sheet1])
GO
-- OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Temp\Pasta1.xls;Extended Properties=Excel 8.0')...[Sheet1]
GO

请注意，要使用 JET 驱动程序，必须将 Office 2016 XLSX 电子表格转换为 Office 2003 XLS 格式，并在查询中将 Excel 版本更改为 8.0。

如果尝试导入 XLSX，我们将看到以下错误消息：

消息 7399，级别 16，状态 1，第 2 行
链接服务器“(null)”的 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”报告错误。提供者没有提供有关错误的任何信息。
消息 7303，级别 16，状态 1，第 2 行
无法初始化链接服务器“(null)”的 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”的数据源对象。

如果尝试导入 XLS，但是没有在查询中将 Excel 版本更改为 8.0，我们将看到以下错误消息：

链接服务器“(null)”的 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”返回消息“找不到可安装的 ISAM。”。
消息 7303，级别 16，状态 1，第 2 行
无法初始化链接服务器“(null)”的 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”的数据源对象。

32位操作系统必须使用 Microsoft.Jet.OLEDB.4.0 驱动，支持 2003 以上版本的 Excel 文件，以上必须使用ACE DB。

安装 Microsoft ACE OLEDB 驱动程序

使用广泛，主要用于支持 64 位操作系统，必须使用以下链接之一安装 ACE 驱动程序：

2007 Office System Driver: Data Connectivity Components (32 bits)

Microsoft Access Database Engine 2010 Redistributable (32 e 64 bits)

sqlserver驱动包maven sql server驱动程序_驱动程序_04

安装完成后，提供程序和驱动程序即可在 SQL Server 中使用（无需重新启动）。查询方法如下：

-- OPENROWSET
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\Pasta1.xlsx', [Sheet1$])
GO
-- OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Temp\Pasta1.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
GO

Microsoft.ACE.OLEDB.12.0 驱动可以在 32 位操作系统下打开 2007 版本以上的 Excel 文件，在 64 位操作系统下可以打开任何版本的 Excel 文件。

请注意，如果安装了 Microsoft Office 2007-2016 x86（32 位），则无法安装 64 位 ACE OLEDB 驱动程序。也就是说，如果你使用的是 64 位 SQL Server 并且安装了 32 位 Microsoft Office，您将无法使用 OPENROWSET/OPENDATASOURCE 函数打开 Excel 文件，并且您可能会遇到以下错误消息：

消息 7403，级别 16，状态 1，第 1 行
OLE DB 提供程序“Microsoft.ACE.OLEDB.12.0”尚未注册。

如何启用分布式事务

使用 OLE DB 提供程序时最常见的错误之一是未启用 Ad Hoc 分布式查询功能。发生这种情况时，您会遇到以下错误消息：

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

很简单，按照提示，执行以下脚本：

EXEC SP_CONFIGURE 'Show Advanced Options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
GO

设置 ACE OLEDB 属性

尝试使用 Microsoft ACE OLEDB 驱动程序时可能出现的另一个问题是未启用 AllowInProcess 和 DynamicParameters 功能，错误消息如下：

如果你遇到这种情况，只需运行以下命令即可启用这些功能：

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

授予对 TEMP 目录的权限（32 位）

这种类型的问题只发生在 SQL Server x86（32 位）上。发生这种情况是因为 SQL Server 在使用提供程序的查询执行期间使用执行查询的用户的凭据创建临时文件。生成的错误消息如下所示：

链接服务器“(null)”的 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”返回消息“未指定错误”。
消息 7303，级别 16，状态 1，第 1 行
无法初始化链接服务器“(null)”的 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”的数据源对象。

如果 SQL Server 使用网络服务帐户运行，则临时目录为：

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

如果 SQL Server 使用本地服务帐户运行，则临时目录为：

C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

在这种情况下，我们必须向该目录中的所有用户或仅向运行此类查询的用户授予读写权限。这可以通过类似于以下的命令来完成：

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant Jeeson:(R,W)

在上面的示例中，我们创建了命令来定义权限，以防 SQL Server 使用 NetworkService 帐户运行并且用于执行查询的用户是“Jeeson”。