通过存储过程实现SQL Server与Excel数据交互时OLE DB报错如何解决

报错解决步骤 #

你遇到的Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)"错误，可按以下顺序排查：

• 修正代码拼写错误：你当前配置语句里的show advenced options拼写错误，正确参数为show advanced options，修正后重新执行配置语句：

sp_configure 'show advanced options', 1;
reconfigure;
go

sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure;
go

• 配置OLE DB提供程序权限：打开SQL Server Management Studio，依次进入服务器对象>链接服务器>访问接口，找到Microsoft.ACE.OLEDB.16.0右键打开属性，勾选允许进程内选项，保存后重启SQL Server服务生效。
• 调整文件存放路径与权限：不要将Excel文件放在C盘根目录，建议新建专门的文件夹（如C:\SqlExcel\），给该文件夹添加Authenticated Users的读写权限，再将Excel文件移入该目录，修改代码里的文件路径为对应路径。
• 确认驱动位数匹配：保证安装的Microsoft.ACE.OLEDB.16.0驱动位数和SQL Server的运行位数一致（32位SQL装32位驱动、64位SQL装64位驱动），和Office的位数无需强制匹配。

排查完成后可重新执行你的读取语句验证：

use [myproject]
go

select * 
from openrowset('Microsoft.ACE.OLEDB.16.0', 'Excel 16.0; Database=C:\SqlExcel\myexcel.xlsx', [sheet1$]);
go

写入Excel实现方法 #

SQL Server向Excel写入数据可直接复用OPENROWSET语法，分为两种常用场景：

1. 追加数据到已有Sheet #

要求Excel中目标Sheet的表头字段、字段顺序、字段类型和SQL查询返回的结果完全匹配：

INSERT INTO OPENROWSET(
    'Microsoft.ACE.OLEDB.16.0',
    'Excel 16.0;Database=C:\SqlExcel\myexcel.xlsx;HDR=YES',
    'SELECT 字段1,字段2,字段3 FROM [sheet1$]'
)
SELECT 字段1,字段2,字段3 FROM 你的SQL业务表名;

参数HDR=YES表示Excel第一行是表头，若Excel没有表头则改为HDR=NO。

2. 直接生成新Sheet并写入数据 #

无需提前在Excel中创建Sheet，执行语句会自动生成新Sheet并写入数据：

SELECT * INTO 
[Excel 16.0 Xml;HDR=YES;DATABASE=C:\SqlExcel\myexcel.xlsx].[自定义新Sheet名称]
FROM 你的SQL业务表名;

内容的提问来源于stack exchange，提问作者Ren