来自 SQL 表的数据透视表查询有时会出错

Question

使用 PowerPivot 10.50.1747.0 运行 Excel 2010，访问 SQL Server 2005 SP2。

我正在修改 Excel 数据透视表。它通过查询从 SQL 表获取数据。我正在对“表属性”下的查询进行更改。当我输入新查询并单击“验证”按钮时，我得到“SQL 语句有效”。如果我单击“设计”按钮，然后在出现的窗口中单击“运行”（“！”）按钮，我将获得正确的数据。但是，当我退出“设计”窗口并单击“保存”按钮时，我收到错误：

==============
The refresh operation failed because the source data base or the table does 
not exist, or because you do not have access to the source

More Details:
OLE DB or ODBC error.
An error occurred while processing the 'Query' table.
The operation has been cancelled.
==============

这里发生了什么？ （我正在使用表变量 - 这会导致问题吗？）感谢您的帮助。

这是代码：

declare @WCsWithDups TABLE(  
 WOStatus Char(1) not null,  
 WOCreated DateTime not null,  
 WorkOrderNo VarChar(15) not null,  
 WorkCenterID VarChar(6) not null,  
 ItemClassID VarChar(12) not null,  
 StockItem Char(1) not null,  
 FirstChar Char(1) not null,  
 MaxWODays SmallInt not null  
)  

insert into @WCsWithDups(WOStatus, WOCreated, WorkOrderNo, WorkCenterID, ItemClassID, StockItem, FirstChar, MaxWODays)  
select DISTINCT  
case 
 when wocompleted is null then 'O' 
 else 'C' end as WOstatus, 
wocreated, 
WorkOrderNo, 
workcenterid, 
itemclassid, 
case when timinventory.minstockqty>0 then 'Y' 
 when timinventory.maxstockqty>0 then 'Y' 
 when timinventory.safetystockqty>0 then 'Y' 
 when timinventory.stdordqty>0 then 'Y' 
 else 'N' end as stockitem, 
left(itemproduced,1) as FirstChar,
(select  max(datediff(day,wocreated,transdate)+1) from vdvMT b where TR1.workorderno=b.workorderno) as maxwodays

from vdvMT as TR1
 left join tInv on TR1.itemkeyproduced=tInv.itemkey and TR1.whsekey=tInv.whsekey
where type='L' and wocreated>=(getdate()-365) and (workcenterid = 'Shed' or workcenterid = 'OP') 

delete from @WCsWithDups
where WorkOrderNo in (
 select WorkOrderNo from @WCsWithDups group by WorkOrderNo having count(WorkOrderNo) = 1);

select distinct WOStatus, WOCreated, WorkOrderNo, 'Shed+OP' as WorkCenterID, ItemClassID, StockItem, FirstChar, MaxWODays 
from @WCsWithDups

Answer 1

正在扫描未回答的问题 - 看起来OP在其他地方得到了回答：

http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/d7c7bc25-d140-4957-9b66-9963d1c655b1

在插入之前设置不计数并在插入后将无计数设置为关闭

PowerPivot 仅支持一个记录集
在你的情况下，通过执行 insert 和
删除你会得到多个结果
设置这可能是问题所在。