Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Powerpivot and Stored Procedure as a SQL Source

Proposed Answer Powerpivot and Stored Procedure as a SQL Source

  • Thursday, March 18, 2010 9:13 PM
     
     
    Sign In to Vote
    0
    In PowerPivot can you use a stored procedure as a SQL query source? I am getting this error "The SQL statement is not valid" error but in the Design button I can pull the records.

    The stored procedure is using temp tables and does not have any parameters.

    I tried to use:
    exec sp_stored_proc
    sp_stored_proc
    dbo.sp_stored_proc
    [dbo].[sp_stored_proc]
    exec [dbo].[sp_stored_proc]
     

All Replies

  • Friday, March 19, 2010 3:51 PM
    Moderator
     
     
    Sign In to Vote
    0

    Based on my test, if the stored procedure does not contain temp table, it should work with either exec spName, or simply spName when you select "Write a query that will specify the data to import". When the stored procedure contains temp table, the import fails in my test. Let me look into further on this and get back to you. At the mean time, can you please send me the stored procedure code you are testing to make sure it is not something else that could also contribute to this problem?

    Thanks,

    Lisa

     
  • Friday, March 19, 2010 7:44 PM
     
     
    Sign In to Vote
    0

    Here is the error I get when I try to use a table variable:

    CREATE PROCEDURE [dbo].[sp_PowerPivot_Test]
    AS
    BEGIN
    -- Table Variable Test
    declare @tablevar table
    (
    salesordid varchar(50)
    ,custname varchar(50)
    )
    insert into @tablevar (salesordid, custname)
    select top 100 SalesOrderID, CustomerName from SalesOrders
    select * from @tablevar
    END

    "OLE DB or ODBC error.

    The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

    An error occurred while the partition, with the ID of 'MS_b5aa935d-f354-4ec2-87e5-365a9a18c06e', Name of 'Query' was being processed.

    The operation has been cancelled."

    Here is the error I get when I try to use a temp table:

    CREATE PROCEDURE [dbo].[sp_PowerPivot_Test]
    AS
    BEGIN
    -- Temp Table Test
    create table #test
    (
    salesordid varchar(50)
    ,custname varchar(50)
    )
    insert into #test (salesordid, custname)
    select top 100 SalesOrderID, CustomerName from SalesOrders
    select * from #test
    drop table #test
    END

    "The SQL statement is not valid."

     
  • Friday, March 19, 2010 9:54 PM
    Moderator
     
     
    Sign In to Vote
    0

    Thank you for sharing your code. I have filed a bug on this and will get back to you as soon as I have some update for you.

    Thanks,

    Lisa

     
  • Tuesday, March 23, 2010 8:17 PM
     
     
    Sign In to Vote
    0
    What time frame should I expect? 1 week, 1 month? I need to report back to my manager.
     
  • Friday, March 26, 2010 7:43 PM
    Moderator
     
     
    Sign In to Vote
    2

    AS product team looked into this and identified the workaround which is to add SET NOCOUNT ON in the stored procedure. The following stored procedure would work.

    create procedure test
    as
    begin
         SET NOCOUNT ON
         create table #temp (c1 int)
         insert into #temp values (1)
         select * from #temp
         drop table #temp
    end

    If the stored procedure returns multiple result sets, only the first result set would be imported and this behavior is by design. For instance, if I change the above stored procedure to below:

    create procedure test
    as
    begin
         SET NOCOUNT ON
         create table #temp (c1 int)
         insert into #temp values (1)
         select * from #temp
         drop table #temp
         select * from dbo.DimProductCategory
    end

    Only the result set (1 row) is imported.

    Thanks,

    Lisa

     
  • Wednesday, March 31, 2010 3:36 PM
     
     
    Sign In to Vote
    0

    The addition of SET NOCOOUNT ON worked for the Table Variable Example but NOT the Temp Table Example.

    In the PowerPivot SQL Query window the intial validation passed but when you click Finish you get:

    "The SQL statement is not valid."

    I even used your exact code (top code set) from above and placed it in a stored procedure and it received the same error:

    "The SQL statement is not valid."

    Thanks for the help I will be able to use table varaibles but would also like to leverage existing code that uses temp tables.

    Thoughts?

    Shawn 

     
  • Thursday, April 01, 2010 4:02 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi, Shawn,

    What build of PowerPivot for Excel you are running? The same code works in my test where I am running RC0 build 10.50.1450.3.

    Thanks,

    Lisa

     
  • Friday, April 09, 2010 4:03 PM
     
     
    Sign In to Vote
    0

    There are few things needs to be kept in mind while using Stored Procedures.

    1) Set No Count on so that you get only one result set arrived after execution.

    2) Make sure all the columns received at the end has proper alias. If you don't provide alias SSMS will automatically assign a name and it also auto assign name if it has duplicates. PowerPivot will not do that, you have to make sure you provide proper column alias and resolve all the duplicates

    3) If your stored procedure takes parameter make sure you pass the parameter values in the query you have specified.

    At the end of the day, you are trying to import it as a table, thus making sure the query you are going to execute stored procedure is sufficient to arrive the result.

    Hope this helps!

    Thanks,

    Deva [MSFT]

    Deva
     
  • Thursday, April 29, 2010 8:39 PM
     
     
    Sign In to Vote
    0

    Lisa,

    My PowerPivot build number is 10.50.1405.0. So the version may be an issue, can you direct me to the install of 10.50.1450.3?

    When is the release of PowerPivot?

    Shawn

     
  • Friday, April 30, 2010 8:23 PM
    Moderator
     
     
    Sign In to Vote
    0
    Bittyboy, you should have the RTM build around the first week of May 2010. Pls test it then and let us know if you're running into the same behaviour.
     
  • Tuesday, July 05, 2011 3:42 PM
     
     
    Sign In to Vote
    0

    is there a way to pass in the filter results from the worksheet back to the datasheet refresh? My procedure is working fine, but i wanted to cut down on the number of rows returned to what the report needs.

    thanks!

     
  • Friday, July 22, 2011 12:16 AM
     
     
    Sign In to Vote
    0

    Hi deva,

    your inputs in this article are very helpful. I have a question.

    my SP follows all the rules that mentioned above except the fact that it uses #temp tables.

    for example:

    SET NOCOUNT ON;

    SELECT [Col1], [Col2]

    INTO #Temp

    FROM Table1

    SELCT * FROM #Temp

    and i am using PowerPivot version 10.50.1747.0.

    but still, Power Pivot says, its invalid SQL statement.

    using #Temp tables is still a problem with latest power pivot version? any suggestions!!

    thanks

    Ramesh Jammanapalli

     

     

     


     
  • Wednesday, August 31, 2011 3:21 PM
     
     
    Sign In to Vote
    0

    I too need to use a stord procedure with temp tables in the procedure.  (I am using the 2500 build of PowerPivot). 

    Using a store procedure with a temp table, I do not see columns in the PowerPivot query designer.  While a SP without a temp table shows columns in the designer.  I guess I am not providing a "proper Alias"?  I have use  final selections with *, with the temp column names and aliased.  None worked.

    As others I think I have followed all the good suggestions.  My procedure (with the temp table) executes fine in SQL Studio and shows the columns I specified.  (FYI: my procedure is structured like the one from Jammanapalli, except that I trie other final selects and am using the current release - 2500.0)

    It would be great if you could give us a simple example, using a temp table, that should work in PowerPivot?

    bob mick
     
  • Wednesday, September 28, 2011 4:07 PM
     
     Proposed Answer
    Sign In to Vote
    1

    i came across this same issue inside SSIS a while back and came across an article related to it with ssrs, http://p2p.wrox.com/reporting-services/64670-cant-use-stored-procedures-containing-temp-tables.html.  i know this is old, but setting the SET FMTONLY OFF worked for me. 

     

    this is the signature of the procedure that i used to test and confirmed worked

    create procedure test as
     set fmtonly off
     set nocount on
     select 1 as ky, 'test' as value
     into #test
     select f.ky, f.value from #test f
     drop table #test 

     

    for the record i did not need to set this for ssrs, but i do not believe it hurts.

    hope this helps

    Robert

    • Proposed As Answer by bob mick Monday, February 20, 2012 2:31 PM
    •  
     
  • Wednesday, September 28, 2011 5:35 PM
     
     
    Sign In to Vote
    1

    I decided to try Robert Harmon's suggestion of "SET FMTONLY OFF", and of course it was a good idea to start with one of my cases that failed. 

    I ran one of the failed cases (that uses Temp tables) and now it works?  Tried the other case that failed previously and it works too?  As far as I know nothing has changed - but the IT group could have applied upates to SQL Server?

    This time, I did use a new Excel file (last time was adding to a workbook with a lot of  PPv tables). Going to take a closer look and see if I changed something else.

    bob mick
     
  • Monday, October 03, 2011 7:12 PM
     
     
    Sign In to Vote
    0

    are you still having problems?

    i was able to get all our situations to work.  if you can provide procedure pattern i can try and look more.

     

    robert

     
  • Monday, February 20, 2012 2:29 PM
     
     
    Sign In to Vote
    1

    Well, I just had to write a new procedure (in support of PowerPivot) that required temp tables, and when it was working fine in Management Studio, found that would not display the column headings in the PowerPivot designer (same ole problem) and usually failed the validity test.  I tried all sort of organizations, selections ... (which should work).

    I then tried Roberts SET FMTONLY OFF suggestion and now it works! Meaning that:

    - I see the columns in the designer, after selecting my procedure, as it should.

    - I get the procedure results in PowerPivot.  (have not examined it carefully yet)

    I went back and started removing other things I tried to see what else might have been required:

    - I changed my last statement from SELECT <enumeration of columns> to SELECT *.  Enumeration was not necessary.  It worked.

    - I removed SET NOCOUNT ON.  Still saw the columns in designer, but failed during execution of the procedure (from PowerPivot). Put it back and everything worked again.

    My conclusion is that there are at least two strange problems in Power Pivot:

    - Looks like PowerPivot needs data to show the columns in the designer and properly build the query? (SET FORMATONLY OFF)

    - Looks like sending back affected row information as the procedure is processed is not handled well by PowerPivot. (SET NOCOUNT ON)

    - May be others?

    BTW:  I am using the RTM ..2500.. version of PowerPivot

    Robert.  REEEAAAALLLLY appreciate your contribution.  That was the key.

    bob mick



    • Edited by bob mick Monday, February 20, 2012 2:30 PM
    • Edited by bob mick Monday, February 20, 2012 2:36 PM
    •  
     
  • Friday, February 24, 2012 6:48 PM
     
     
    Sign In to Vote
    0

    chenthorn,

    There is no way to have filters (slicers or rows and columns in a pivot table) apply to the data refresh in the PowerPivot window (where data is collected, and the dataset an be reduced).

    Hope I understood your question,

    bob

    bob mick

     
  • Wednesday, October 24, 2012 12:17 AM
     
     
    Sign In to Vote
    0

    I've tried all the options stated and still having issues.  As I comment sections of my procedure out, I'll get the field lists but as I uncomment items that don't even reference tables, temps or physical, the list disappears.

     
  • Thursday, January 24, 2013 10:09 PM
     
     
    Sign In to Vote
    0

    If you're using SQL Server 2005 or higher, for most procedures, you can get around the temp table issue by using Common Table Expressions: (CTEs) http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx  Sorry I can't include links until my account is verified...