Search code examples
excelsql-server-2008export-to-excel

Exporting to Excel from SQL Server getting error

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.

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

    references here