ODS query from Excel returns spurious column names

When retriving Excel sheet Named region it returns spurious column names

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT `Tbl1005`.`CUSTOMER` AS `Col1031`,`Tbl1005`.`NAME` AS `Col1032`,`Tbl1005`.` 1AA00` AS `Col1033`,`Tbl1005`.` 1AB00` AS `Col1034`,`Tbl1005`.` 1AC00` AS `Col1035`,`Tbl1005`.` 1AD00` AS `Col1036`,`Tbl开发者_如何学Python1005`.` 1AE00` AS `Col1037`,`Tbl1005`.` 1AF00` AS `Col1038`,`Tbl1005`.` 1AG00` AS `Col1039`,`Tbl1005`.` 1AH00` AS `Col1040`,`Tbl1005`.` 1AL00` AS `Col1041`,`Tbl1005`.` 1AM00` AS `Col1042`,`Tbl1005`.` 1AN00` AS `Col1043`,`Tbl1005`.` 1AO00` AS `Col1044`,`Tbl1005`.` 1AP00` AS `Col1045`,`Tbl1005`.` 1AQ00` AS `Col1046`,`Tbl1005`.` 1ZA00` AS `Col1047`,`Tbl1005`.` 1ZD00` AS `Col1048`,`Tbl1005`.` 4AN00` AS `Col1049`,`Tbl1005`.` 4AO00` AS `Col1050`,`Tbl1005`.` 4ZB00` AS `Col1051`,`Tbl1005`.` 5ZA00` AS `Col1029` FROM `CPGROUPS` `Tbl1005`" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

How do I reslove this ?

---

I ran into this with a sheet that had leading spaces on the first row, used as the field name. The spreadsheet was setup as a linked server

The error message was:

Msg 7320, Level 16, State 2, Line 1 Cannot execute the query "SELECT Tbl1002.Participant AS Col1019 FROM DATASET$ Tbl1002" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXX".

After the leading space was removed it worked.

The linked server was setup using:

exec sp_addlinkedserver @server = N'XXXXX', 
@srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', 
@datasrc=N'\\somewhere\some_dir\mysheet.xlsx',
@provstr=N'EXCEL 12.0;Hdr=Yes' ;

---

I received this same error for a query to dBase. The error is caused by hitting the 255-column limit for the query result from OPENDATASOURCE. I believe the message is just from an internal call for SQL Server loading the results into memory and parsing them into a result set. They don't represent real column names or anything related to the final output.

I resolved the issue in my case by specifying a hard-coded column list with less than 255 total field names.

---

Hard coding column names is not an option for me as I would neither know number of columns nor their names before hand. I resoloved the issue by specifying

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source="\xxxx\x.xls";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...cpGroups

and Allow Inproc = True, Level Zero only=1 on the Provider (ACE.Oledbb.12.0)in the SQL Server

Hope what i learnt can help somebody

Regards

---

The error went away for me when I removed leading spaces in field names as mentioned by @Jim, i.e. column headings in the spreadsheet , but also:

• Duplicated field names - I think this was the biggest problem
• Trailing field names