sql server代理job导入excel报错

Question

版本：sql server 2016

insert into PPC.dbo.sheet1
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=xxxxx.xls;Extended Properties="Excel 12.0"',
                 'SELECT * FROM [sheet1$]')

以上代码手动执行成功了，但是用代理job自动执行就报错。

上图设置时报错：已以用户 NT SERVICE\SQLSERVERAGENT 的身份执行。 链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0" 报错。提供程序未给出有关错误的任何信息。 [SQLSTATE 42000] (错误 7399) 无法初始化链接服务器“(null)”的 OLE DB 访问接口“Microsoft.ACE.OLEDB.12.0”的数据源对象。 [SQLSTATE 42000] (错误 7303). 该步骤失败。

当把上图选项都不选上的时候报错：已以用户 NT SERVICE\SQLSERVERAGENT 的身份执行。 无法创建链接服务器“(null)”的 OLE DB 访问接口“Microsoft.ACE.OLEDB.12.0”的实例。 [SQLSTATE 42000] (错误 7302). 该步骤失败。

Answer 1

引自免费微信小程序：皆我百晓生

在使用 SQL Server Agent 代理作业时执行涉及到 OLE DB 提供程序的操作（例如，通过 OPENROWSET 导入 Excel 文件）时，可能会遇到权限和配置问题。根据您提供的描述和错误信息，这里有几个可能的解决方案和建议：

1. 权限问题

错误提示提到了 NT SERVICE\SQLSERVERAGENT 这个用户。这是 SQL Server Agent 服务的默认账户。在某些情况下，这个账户可能没有访问文件系统或注册表的必要权限，从而导致 OLE DB 提供程序无法正常工作。您可以尝试以下步骤：

解决步骤：

• 确保 SQL Server Agent 服务的账户有足够的权限来访问包含 Excel 文件的文件夹。您可以尝试为 NT SERVICE\SQLSERVERAGENT 用户分配文件夹的读取权限。
• 确保 SQL Server Agent 有足够的权限来注册和使用 OLE DB 提供程序。您可能需要检查注册表的相应位置，并确保 SQL Server Agent 服务账户有写入注册表的权限。

2. OLE DB 提供程序配置

错误可能也与 OLE DB 提供程序的配置有关。您可能需要检查您的 SQL Server 配置，确保 Microsoft.ACE.OLEDB.12.0 提供程序已正确安装和配置。

解决步骤：

• 确保在 SQL Server 上安装了正确版本的 ACE OLE DB 提供程序（例如，Microsoft Access Database Engine）。您可能需要重新安装或更新此组件。
• 检查 SQL Server 的配置设置，确保 OLE DB 提供程序已正确配置并可用于 SQL Server Agent。

3. 使用不同的方法导入 Excel 文件

如果上述方法都不能解决问题，您可以尝试使用不同的方法来导入 Excel 文件到 SQL Server。例如，您可以考虑使用 SQL Server Integration Services (SSIS) 或第三方工具来导入 Excel 文件。这样可能更容易管理和控制权限问题。

模拟图（根据需要生成）

由于问题涉及配置和权限，这里无法直接生成模拟图来展示具体的设置步骤。但根据上述建议，您可以根据这些步骤在您的环境中进行配置和调整。

代码示例（如果需要）

如果您在手动执行代码时没有问题，那么在代理作业中使用相同的代码应该也能成功执行，前提是上述问题都已解决且配置正确。无需更改代码本身。如果仍有问题，请提供更多的信息以便进一步分析。

总结和建议的行动步骤

• 检查并确保 SQL Server Agent 服务账户的权限设置正确。
• 检查 OLE DB 提供程序的安装和配置情况。
• 考虑使用其他方法导入 Excel 文件到 SQL Server。
• 如果问题仍然存在，请提供更多的错误信息或日志以便进一步分析。