Share via

Report won't return values from procedure unless it is recompiled

Crescentia Stegner-Freitag 101 Reputation points
2020-09-02T15:52:41.807+00:00

I started seeing strange behavior in a report yesterday and don't understand what's going on. I'm hoping for ideas.

Situation:
I have an rdl report with a dataset that calls a stored procedure. Yesterday I noticed that the report stopped returning values for some of the fields returned by this procedure when I ran it against the production server. However, when I executed the procedure directly in SSMS or ran the report against the test server, I get the expected values for all columns. The only workaround I've found is to force the procedure to recompile.

I did modify the procedure recently (I changed the conditions in multiple joins). I have made similar changes previously with no issue.

I am reasonably certain this is not an example of SSRS report caching because the issue occurred with multiple parameter options and in newly-created rdl files.

I'm using SQL Server 2019, PowerBI September 2019. I normally create and modify reports directly in Report Builder, though I was also able to reproduce the issue for reports built in Visual Studio.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,860 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,571 Reputation points
    2020-09-03T02:51:04.417+00:00

    Hi,
    It is a bit strange because SSRS dataset query is mainly doing the same thing as in SSMS.

    What are the errors you get when you run the dataset in VS / Report Builder?

    After you recompile the SP, did everything go on well? Then, it could be the workaround, since some situations the SP could not be recompile automatically and need to be forced. See here : Recommendations
    Regards,
    Joy

    0 comments
  2. Joyzhao-MSFT 15,571 Reputation points
    2020-09-21T07:54:49.127+00:00

    Hi @Crescentia Stegner-Freitag ,
    Has your issue been resolved?
    If you have any question, please feel free to let me know.
    Regards,
    Joy

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments