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`,`Tbl1005`.` 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 ?
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