Temp table or table variable in query (not stored procedure), possible?

Question

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

Simple example:

    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.

Message:

OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

Could you please help?

Many thanks

Questions:

-

Answer 1

Update: it's the insert !!

If I remove the second instruction, I don't get an error... but it's useless !

---

Rob.be

Answer 2

I get the same error.  Maybe you have to do it in a stored procedure and call the stored procedure in PowerPivot?

Answer 3

Seems not a solution: I have no priviledge to create procedures on the DB and our policy will not be changed...

---

Rob.be

Answer 4

I 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