从SQL Server导出到Excel收到错误(Exporting to Excel from SQL Server getting error)

我有这个脚本工作正常

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess' , 1 RECONFIGURE EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 RECONFIGURE INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;hdr=yes;Database=C:\aa\Customer.xlsx;','SELECT * FROM [Hoja1$]') select a.Email from AspNetUsers a

这工作正常，并在我现有的Customer.xlsx电子表格中获取一列，但是当我尝试从表中选择多个列时

select a.Email, a.Id from AspNetUsers a

我得到了以下错误

Msg 213, Level 16, State 1, Line 5 Column name or number of supplied values does not match table definition.

我已经尝试了一切，但我无法弄清楚为什么会发生这种情况。

我的想法是使用这样的东西导出到excel整个结果集。

Select * from AspNetUSers

我得到了同样的错误。

我一直在寻找几个小时，我发现没有什么好理解这个。

我正在使用SQL Server 2014和2008两者都得到相同的错误。

提前致谢。

I have this script that's works fine

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess' , 1 RECONFIGURE EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 RECONFIGURE INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;hdr=yes;Database=C:\aa\Customer.xlsx;','SELECT * FROM [Hoja1$]') select a.Email from AspNetUsers a

This works fine and get a column in my existing Customer.xlsx spreadsheet but when I try to select several columns from the table

select a.Email, a.Id from AspNetUsers a

I get the folowing error

Msg 213, Level 16, State 1, Line 5 Column name or number of supplied values does not match table definition.

I've tried everything but I can't figured out why this happening.

My idea is to export to excel an entire resultset using some like this.

Select * from AspNetUSers

And I get the same error.

I've been searching for hours I don't found something good to understand this.

I'm using SQL Server 2014 and 2008 In both get the same error.

Thanks in advance.

最满意答案

只需打开Excel即可 为Sheet1的第一行中的每列提供列名称 保存Excel 再次执行脚本..它会工作！

这里引用

Just open the Excel Give column name on each columns in first Row of Sheet1 save the Excel execute the script again.. It'll work!

references here

从SQL Server导出到Excel收到错误(Exporting to Excel from SQL Server getting error)

我有这个脚本工作正常

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess' , 1 RECONFIGURE EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 RECONFIGURE INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;hdr=yes;Database=C:\aa\Customer.xlsx;','SELECT * FROM [Hoja1$]') select a.Email from AspNetUsers a

这工作正常，并在我现有的Customer.xlsx电子表格中获取一列，但是当我尝试从表中选择多个列时

select a.Email, a.Id from AspNetUsers a

我得到了以下错误

Msg 213, Level 16, State 1, Line 5 Column name or number of supplied values does not match table definition.

我已经尝试了一切，但我无法弄清楚为什么会发生这种情况。

我的想法是使用这样的东西导出到excel整个结果集。

Select * from AspNetUSers

我得到了同样的错误。

我一直在寻找几个小时，我发现没有什么好理解这个。

我正在使用SQL Server 2014和2008两者都得到相同的错误。

提前致谢。

I have this script that's works fine

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess' , 1 RECONFIGURE EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 RECONFIGURE INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;hdr=yes;Database=C:\aa\Customer.xlsx;','SELECT * FROM [Hoja1$]') select a.Email from AspNetUsers a

This works fine and get a column in my existing Customer.xlsx spreadsheet but when I try to select several columns from the table

select a.Email, a.Id from AspNetUsers a

I get the folowing error

Msg 213, Level 16, State 1, Line 5 Column name or number of supplied values does not match table definition.

I've tried everything but I can't figured out why this happening.

My idea is to export to excel an entire resultset using some like this.

Select * from AspNetUSers

And I get the same error.

I've been searching for hours I don't found something good to understand this.

I'm using SQL Server 2014 and 2008 In both get the same error.

Thanks in advance.

最满意答案

只需打开Excel即可 为Sheet1的第一行中的每列提供列名称 保存Excel 再次执行脚本..它会工作！

这里引用

Just open the Excel Give column name on each columns in first Row of Sheet1 save the Excel execute the script again.. It'll work!

references here