351
Like
Share
ReportI am working on a PowerPivot based analysis design and my data source is a SQL Stored Procedure which does the following:
1. Inserts set of records from one DB into a Table Variable.
2. Inserts similarly structured set of records from another DB into the same Table Variable.
3. Finally retrieves records by SELECT...GROUP BY... statement based on necessary criteria.
The above is to ensure that I don't end up troubleshooing Temp table issues or data redundancy or performance issues for that matter.
PowerPivot understands the above stored procedure very clearly and it also validates the execute query without any issues. It even shows the result set in preview. But it throws the following error message after all the steps involved in setting up SP as data source:
1. Inserts set of records from one DB into a Table Variable.
2. Inserts similarly structured set of records from another DB into the same Table Variable.
3. Finally retrieves records by SELECT...GROUP BY... statement based on necessary criteria.
The above is to ensure that I don't end up troubleshooing Temp table issues or data redundancy or performance issues for that matter.
PowerPivot understands the above stored procedure very clearly and it also validates the execute query without any issues. It even shows the result set in preview. But it throws the following error message after all the steps involved in setting up SP as data source:
The above error message is quite less informative. I initially thought Stored Procedures are not properly supported by PowerPivot (how silly I am...!!!). But I just gave myself some more time to search forums as usual. After all, I spent almost a day in getting that Stored Procedure completed with 100% accurate results.
The following is the SIMPLE FIX to that menacing issue:
Yeah that's it. SET NOCOUNT ON is that simple fix. Now my PowerPivot understands this SP and works merrily.
The Post I got this answer from: PowerPivot and Stored Procedure as a SQL Source.
The answer is given by Microsoft Product Team and that was conveyed by Lisa Liu CSS, a Microsoft Moderator.
The reason for why we ought to set NOCOUNT ON is given in the same post by Devarajan KM. The reason is: Set NOCOUNT to ON so that you get only one result set arrived after execution.
VAIDY
*This post is locked for comments