sql-server-2005 - 来自 Excel 的 ODS 查询返回虚假的列名

Question

检索 Excel 工作表命名区域时，它返回虚假列名

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)".

我如何重新喜欢这个？

Answer 1

我在第一行有前导空格的工作表中遇到了这个问题，用作字段名称。电子表格被设置为链接服务器

错误消息是:

消息 7320，级别 16，状态 2，第 1 行
无法执行查询“SELECT Tbl1002 . Participant AS Col1019发件人 DATASET$ Tbl1002 ” 针对链接服务器“XXXXX”的 OLE DB 提供程序“Microsoft.ACE.OLEDB.12.0”。

删除前导空间后，它起作用了。

链接服务器是使用以下方法设置的:

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' ;