sqlserver 用脚本的方式将数据导入到excel中，各种踩坑_the ole db provider "microsoft.ace.oledb.12.0" for-CSDN博客

本文链接：https://blog.csdn.net/weixin_44738797/article/details/106746861

表结构

table1只有两列id 和name,

CREATE TABLE [dbo].[table1](
	[id] [smallint] NULL,
	[name] [nvarchar](20) NULL
)

数据只有条：
在这里插入图片描述

只用到一条sql 语句：

use localDB0001
go

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1 --开启Ad Hoc Distributed Queries组件，默认是关闭的
RECONFIGURE

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\testing.xlsx;','SELECT * FROM [Sheet1$]') 
SELECT * FROM dbo.table1

tips：

需打开 ‘Ad Hoc Distributed Queries’ 组件的访问。默认 SQL Server 阻止了对组件 ‘Ad Hoc Distributed Queries’ 的 STATEMENT’OpenRowset/OpenDatasource’ 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure启用 ‘Ad Hoc Distributed Queries’。有关启用 ‘Ad Hoc Distributed Queries’ 的详细信息，请参阅 SQL Server 联机丛书中的 “外围应用配置器”。
语句运行报错 ?

Msg 7399, Level 16, State 1, Line 12
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 7303, Level 16, State 1, Line 12
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

在这里插入图片描述
注意一定要关闭excel文件，sql 才可以正常运行！

如果sqlserver有这个报错信息： Msg 7303, Level 16, State 1, Line 12 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".
可能之一：没安装linkedserver, 到官网下载 accessdatabaseEngine.exe 这只是一个driver ：
https://www.microsoft.com/en-us/download/details.aspx?id=13255

安装好后server objects–>linked server 中会有 Microsoft.ACE.OLEDB.16.0这一项，如果没有就得安装了！

注意32bit/64bit别安装错了，但我怎么装都是错的!? 为何呢？因为我的ssms是64位的，windows 系统也是64位的，但我的Import/Export Data工具，也就是你在sql server 随意选择一个database 右键export data… 调用出来的wizard窗口，是32bit 的！！！尼玛。。这就注定我安装32位或者64位的都不行？？为何是这样的？我当初是怎么装成功的。。。这么不兼容的存在。也就是我可以手动export 到excel，但无法运行脚本导出数据。
在这里插入图片描述
解决方案：先安装32位的（这点要follow import/export data这个工具）而后下载64位AccessDatabaseEngine_X64.exe 补装，但别双击，而是用命令行的方式打上64位的补丁：网友的参考：https://stackoverflow.com/questions/29567548/microsoft-ace-oledb-12-0-cannot-be-loaded-in-process-on-a-64-bit-sql-server

"C:\Users\rshuell001\Downloads\AccessDatabaseEngine_x64.exe" /passive

excel文件必须存在，要事先创建好，否则sql脚本也会报错。
excel 各个列的列名要提前写好，这个id 和name 是我创建文件之后就手动敲入的，没有列名也会报错。