Rules for calling stored procedures from PowerPivot?

[srieta619]

I've pretty well versed in T-SQL development and am working with a client that is using PowerPivot to call SQL Server stored procedures that i have created. For some of the stored procedures that involve table variables, temp tables and cursors, with the resultset being a physical table joining to a table variable, the user gets the infamouse ' no columns detected in the statement'. The user is using the Wizard to select the stored procedure. They see the parameters but nothing in the field area. I tried to go into the stored procedure and just do a basic select from an existing table and I see the columns. My question is; What are the rules for calling procedures from Power Pivot? I'm attempting to use the SQL Server to do the bulk of the work and have PowerPivot just pull the resultset. Can I not use table variables or temp tables?

 

[RoryA]

This is a bit of a guess based on the way ADO behaves with such SPs but have you tried adding a SET NOCOUNT ON statement to the start of the SPs?

 

[srieta619]

Hi Rory, i did add the 'set nocount on' but that didn't help with using table variables our temp tables. My issue is multi users can call the same procedure at the same time but with different parameters.

 

[srieta619]

So, is it me or does PowerPivot NOT recognize @TableVariables, #TempTables, ##GlobalTempTables? If it only recognizes physical tables it's pretty useless correct? I mean what if you want your stored procedure call to only work with it's own set/scope of data without interfering with other users' calls?