I recently updated a Stored Procedure; I changed the last part of it from this:
SELECT TC.PlatypusDESCRIPTION, TC.MemberName, TC.WEEK1USAGE, TC.WEEK2USAGE, TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
ORDER BY TC.PlatypusDESCRIPTION, TC.MemberName;
...to this:
SELECT TC.PlatypusDESCRIPTION, TC.MemberName, SUM(TC.WEEK1USAGE), SUM(TC.WEEK2USAGE),
SUM(TC.USAGEVARIANCE), AVG(TC.WEEK1PRICE), AVG(TC.WEEK2PRICE), AVG(TC.PRICEVARIANCE),
SUM(TC.PRICEVARIANCEPERCENTAGE)
FROM #TEMPCOMBINED TC
GROUP BY TC.PlatypusDESCRIPTION, TC.MemberName
ORDER BY TC.PlatypusDESCRIPTION;
...to eliminate some duplicate records I was getting (the PlatypusDESCRIPTION and MemberName combination should always appear on one row).
It works just fine from Server Explorer; I can execute the SP, provide the parameters, and it returns the data I expect.
However, the SSRS report that uses this SP no longer works - it leaves the values for all columns from TC.WEEK1USAGE on blank (only the values for PlatypusDESCRIPTION and MemberName display); and when I run it from my custom Winforms app that calls the SP, it gives me an error message about the table not having a field named "" What?!? That field is there, as you can see.
Why would the SP run in Server Explorer, but balk when called from SSRS and elsewhere?