Thursday, July 19, 2012 8:55 PM
I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure)
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
declare @tTable(col1 int)
insert into @tTable(col1) values (1)
select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
Could you please help?
Thursday, July 19, 2012 9:14 PM
Update: it's the insert !!
If I remove the second instruction, I don't get an error... but it's useless !
Friday, July 20, 2012 4:27 AMI get the same error. Maybe you have to do it in a stored procedure and call the stored procedure in PowerPivot?
Friday, July 20, 2012 8:25 AMSeems not a solution: I have no priviledge to create procedures on the DB and our policy will not be changed...
Tuesday, July 31, 2012 11:30 PMI think the reason it fails in PowerPivot is that PowerPivot only allows single result to be returned. You have to make it a stored proc or create a physical table to do this.
-- This posting is provided "AS IS" with no warranties, and confers no rights