将数据从SQL导出到Excel [英] Exporting data from from SQL into Excel

问题描述

我试图从名为"fams"的表中删除数据。在名为"sample"的数据库中到名为Data.xlsx的Excel文件

Excel文件位于文件目录"C：\ Temp"中

Excel文件已经存在在第一行中有列标题。

我使用以下SQL代码来实现此目的：

设置NOCOUNT ON; 
 
 Exec sp_configure'show advanced options'，1; 
 RECONFIGURE; 
 GO 
 
 Exec sp_configure'Ad Hoc Distributed Queries'，1; 
 RECONFIGURE; 
 GO 
 
 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 
 
插入OPENDATASOURCE（'Microsoft.ACE.OLEDB.12.0'，
'数据源= C：\Temp \ Data.xlsx;扩展属性= Excel 12.0'）... [Sheet1 $] 
 SELECT * FROM sample..fams 
  -  Sheet应该已经与标题一起出现
 
 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'，N'AllowInProcess'，0; 
 GO 
 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'，N'DynamicParameters'，0; 
 GO 
 
 Exec sp_configure'Ad Hoc Distributed Queries'，0; 
 RECONFIGURE; 
 GO 
 
执行sp_configure'显示高级选项'，0 
 RECONFIGURE; 
 GO 

但是我收到一条错误消息：

 OLE DB provider" ; Microsoft.ACE.OLEDB.12.0"对于链接服务器"（null）"返回消息"未指定的错误"。 
消息7303，级别16，状态1，行16 
无法初始化OLE DB提供程序的数据源对象"Microsoft.ACE.OLEDB.12.0"。对于链接服务器"（null）"。

我尝试了以下操作：

1）。安装64位Microsoft Office

2）。给"每个人"对我的Excel文件所在文件夹的完全读/写访问权限

解决方案

检查这是否有帮助

https：// visakhm.blogspot.com/2013/12/how-to-solve-microsoftaceoledb120-error.html

Hi,

I am trying to chuck data out from a table called "fams" in a database called "sample" into an Excel file called Data.xlsx

The Excel file resides in the file directory "C:\Temp"

The Excel file already has the columns headings in the first row.

I am using the following SQL code to achieve this:

SET NOCOUNT ON;

Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

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

Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
           'Data Source=‪C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
SELECT * FROM sample..fams
-- Sheet Should be already Present along with headers

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

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

but I get an error message saying:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 16
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I tried doing the following:

1). Installed 64 bit Microsoft Office

2). Gave "Everyone" full read/write access to the folder in which my Excel file lives

解决方案

check if this helps

https://visakhm.blogspot.com/2013/12/how-to-solve-microsoftaceoledb120-error.html

这篇关于将数据从SQL导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！