1

Firstly some background on the environment..

  • RDL's are designed in Report Builder 3.0 (The pre-2016 one).
  • RDL's are hosted on what appears to be an SSRS 2014 Reporting Services server (Reason why I am saying appears to be is because the alias of the Report Manager and Web Service URL is "SSRS_2014" (And the DBA's told me so)).
  • Our database server is either running SQL Server 2014 or 2016. I am running SQL Server 2017 on my workstation.

The problem:

I have an SSRS report data set that retrieves information from a very standard stored procedure. Recently I had to change one line of code and add a column related to this change to the result set. Stored procedure works as expected when testing it in SSMS Query Analyser. Here’s an excerpt of the intended results:

SSMS Query Analyser Results

But after refreshing the dataset (which adds the new column) the dataset now returns inconsistent values under two columns for all the records returned whilst the stored procedure that retrieves the data is returning values when I run it in SSMS. Even when I run it in Query Designer it still returns inconsistent values.

Here’s a screenshot:

Report Builder Query Designer

This is not a shared data set and from what I can gather the report does not have any caching applied. When trying to see if there is, or when trying to setup caching the Report Manager returns the following error:

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote Errors.

As below:

Report Manager Processing Options Error Report Manager Cache Refresh Options Error

This behaviour is occurring even under the following circumstances:

  • I deleted and added the data set.
  • I deleted and added the data source.
  • I created a new data source.
  • I created a new RDL.
  • I created a copy of the stored procedure (Renamed slightly).
  • I added copies of these columns (Renamed slightly) to the stored procedure (They behave the same).
  • Fiddled with the “Use single transaction when processing the queries” option (Was off).

The clinchers: (Bear in mind that the stored procedure returns the data correcting in SSMS). - When I hard-code the values in the stored procedure, these values appear when executing the stored procedure via SSMS Query Analyser but not when running it via Query Designer (Same inconsistent values).
- I have another (summary) report that obtains data from this stored procedure (Embedded) via another procedure. That report returns the data correctly. - I tried to create a second stored procedure that executes this stored procedure (Similarly to what the “summary” one does) and it still misbehaves. - When I take the script that I use to test the stored procedure in SSMS Query Analyser it returns the values! But this is not ideal because the parameters are defined and when I remove those parameters it goes back to normal (mis)behaviour.

I was considering adding a type of snapshot table. Where the stored procedure would first build the data and then do a select from this table to return the data. The problem here is this report is run my multiple users and I do not have the time (3 days before Christmas and I am already on Christmas leave) do go and design a whole snapshot system. I did a lot of research on the internet yesterday. And went through all suggestions given in the following sites/forums to no avail:

Why is my SSRS report showing old data?

SSRS: field shows correct in query but wrong in report preview

How to clear cache of 1 stored procedure in sql server

https://jazz.net/forum/questions/243993/report-builder-not-showing-updated-data

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e6f86f15-420c-4b64-bc70-01dea93a0995/report-results-and-query-results-returning-different-number-of-rows-why?forum=sqlreportingservices

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9bc13904-a727-4786-9a11-693570f5f8d4/diable-the-cache-for-all-reports-in-ssrs-2008?forum=sqlreportingservices

And various other ones about caching and so on.

I even got the DBA’s to restart that SSRS server to see if that might refresh any forms of caching. I also added “WITH RECOMPILE” hint to the stored procedure to no avail.

So I am at a loss… I am at this stage thinking of taking the Stored procedure’s code and running it as an embedded query but that is not best practice. We are also in the process of handing this project over to another company so I don’t want to leave them this (I have my pride).

WhatsThePoint
  • 3,395
  • 8
  • 31
  • 53
Wiaan van Aswegen
  • 71
  • 1
  • 9
  • If you have not already done so, please try deleting the .data files and the .rptproj.rsuser files (in that order) for the directory. This can sometimes solve a multitude of problems! Also, if you are just using read-only stored procedures [which should be the case for most SSRS work] you should consider using functions for the SQL instead. – JosephDoggie Jan 08 '19 at 20:56
  • 1
    Hi there. Thanks for the suggestions. I will see where I can find these data files. But I doubt that they have any because these are designed on report builder 3.0 and hosted via an SSRS server (uploaded via the report Manager). I am using read only SP's for these reports but it is quite a complex one returning alot of data and already taking a while to generate. I ended up Rolling back the changes (fixing everything luckily). Will tinker with it some other time. – Wiaan van Aswegen Jan 08 '19 at 22:36

0 Answers0