none
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:

    -

    Thursday, July 19, 2012 8:55 PM

Answers

All replies

  • Update: it's the insert !!

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


    Rob.be

    Thursday, July 19, 2012 9:14 PM
  • I 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 4:27 AM
  • Seems not a solution: I have no priviledge to create procedures on the DB and our policy will not be changed...

    Rob.be

    Friday, July 20, 2012 8:25 AM
  • 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

    Tuesday, July 31, 2012 11:30 PM
  • I think Chu Xu is correct, only a single result can be returned and the insert is returning a row count.

    If you add SET NOCOUNT ON at the beginning of your statement it will work.

    Your example becomes:

     SET NOCOUNT ON;

    DECLARE @t TABLE(col1 INT);

    INSERT INTO @t(col1)
    VALUES(1);

    SELECT *
      FROM @t;

    Saturday, October 26, 2013 11:53 PM
  • This seems a very simple and interesting idea. I'm not able to test it for now but will surely do next week, thanks!

    Rob.be

    Wednesday, October 30, 2013 4:34 PM
  • this one worked for me:

    SET NOCOUNT ON;
    
    DECLARE @tTable TABLE (col1 int);
    INSERT INTO @tTable(col1) VALUES (1);
    
    SELECT * 
    FROM @tTable

    Seems that the "SET NOCOUNT ON;" does the trick!

    -gerhard


    - www.pmOne.com -

    Wednesday, October 30, 2013 7:36 PM
    Answerer
  • Yes! Finally got time to check: 100% correct.

    Great thanks


    Rob.be

    Monday, November 18, 2013 10:14 AM
  • I think Chu Xu is correct, only a single result can be returned and the insert is returning a row count.

    If you add SET NOCOUNT ON at the beginning of your statement it will work.

    Your example becomes:

     SET NOCOUNT ON;

    DECLARE @t TABLE(col1 INT);

    INSERT INTO @t(col1)
    VALUES(1);

    SELECT *
      FROM @t;

    It is worth noting that when using ##TempTables, PowerPivot will not validate the query, but it will run it.  When you click Validate you will get an error that says "No columns in the select", but, it will execute fine when you click save.  I wasted so much time on that today, had to post. 
    Monday, October 12, 2015 10:41 PM