[SQL] SSRS Dataset fields disappear when calling updated SP

I am trying to add 2 columns to the output of a stored procedure written in 2015, which is running on a SQL Server 2012 instance and is called from an SSRS report on a 2012 server.

The dataset runs as

--EXEC server1.dbo.SP2015
--@param1,
--@param2
--etc


After I modify the SP on the server and try to refresh the SSRS dataset to include the 2 new columns all of the fields disappear, with no error message.

The SP isn't overly complex but it does provide the output with a dynamic query, which apparently has been not a problem all these years until I added a join to pull in the 2 columns today.

I experienced something similar a few years back, where an RDL that had been running fine for years suddenly had the fields disappear when you even added a carriage return to the code in Visual Studio. I ended up refactoring that report as it had a ton of IF-THEN statements when it only needed one IF-THEN.

Seems like I may have to try to refactor the current SP as well, although I don't see how yet.

Just wanted to see if anyone else had experienced this and if there were any known reason why older code that ran fine for years is now getting rejected by Visual Studio.

Continue reading...