Why do I have to redeploy my Report files to avoid errors?

For a while now, I have created SSRS Reports using SQL Server Data Tools and deployed them to the built-in reporting server for users to view with a web browser (IE, Chrome, or Firefox, depending on the user). This last week, I have been consistently getting errors like this.

The strange thing is that when in Data Tools, I can view the report just fine and when I redeploy everything works great... until a few hours later when the same error pops up and I need to redeploy.

I did research on this error and have found nothing helpful to me.  The fact that this works great until some random moment when I need to redeploy tells me it's not a problem with my query, otherwise it wouldn't have worked from the beginning. 

This is possibly a new bug in Data Tools? I cannot seem to get past this error and it's a bi

August 20th, 2015 11:52am

For a while now, I have created SSRS Reports using SQL Server Data Tools and deployed them to the built-in reporting server for users to view with a web browser (IE, Chrome, or Firefox, depending on the user). This last week, I have been consistently getting errors like this.

The strange thing is that when in Data Tools, I can view the report just fine and when I redeploy everything works great... until a few hours later when the same error pops up and I need to redeploy.

I did research on this error and have found nothing helpful to me.  The fact that this works great until some random moment when I need to redeploy tells me it's not a problem with my query, otherwise it wouldn't have worked from the beginning. 

This is possibly a new bug in Data Tools? I cannot seem to get past this error and it's a big

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 12:00pm

@Daniel -- I did as you suggested and created a new report with `SELECT DB_NAME() as DB` the dataset. It confirmed that the correct database was pointed to.

If it were a case of the query pointing to the wrong database, wouldn't the report have failed in the Review panel?

August 20th, 2015 12:09pm

@Daniel -- I did as you suggested and created a new report with `SELECT DB_NAME() as DB` the dataset. It confirmed that the correct database was pointed to.

If it were a case of the query pointing to the wrong database, wouldn't the report have failed in the Review

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 12:30pm

The dataset is a query pointing to a table that is truncated and repopulated every midnight, local time.

Users don't start using the website until 7 AM that day, which means that the database refreshes were done hours beforehand.

As for the query you posted, I'm not sure what I'm looking for specifically, but the database and table names make sense because these are the ones I have to refresh every day.

August 20th, 2015 12:36pm

I wonder if there is some cleanup going on on, like treating the published reports as a cache? I rechecked Reporting Services Configuration Manager and could not find any such settings.

Then I noticed this in the Report Manager under Site Settings.  I don't remember fiddling with "Report Timeout" but I wonder if this has something to do with it.

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 12:50pm

I wonder if there is some cleanup going on on, like treating the published reports as a cache? I rechecked Reporting Services Configuration Manager and could not find any such settings.

Then I noticed this in the Report Manager under Site Settings.  I don't remember fiddling with "Report Timeout" but I wonder if this has something to do with it.

August 20th, 2015 1:00pm

That table name is not in the query result.  I ran it as a distinct query just in case I was missing something. 

I knew that the table existed at the same time as the error appeared, because that was part of my first step in troubleshooting. I was able to use the exact same query in Management Studio without any problem whatsoever.

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 1:12pm

Since I know that redeploying treats the symptom, if not the cause, I wonder if a workaround is to automate deployment on a schedule.
August 20th, 2015 1:21pm

Since I know that redeploying treats the symptom, if not the cause, I wonder if a workaround is to automate deployment on a schedu
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 1:33pm

  • Does this only happen with one report?

This happens with all reports now.

  • Does the report run in the data tools successfully when the report is broken in prod?

It always works in Data Tools. I re-verify it still works in Data Tools and redeploy.

  • Have you disabled caching in the report properties in Report Manager?

No. I'll have to look up how to do that. I didn't see anything obvious the first time around.

  • Have you reviewed the SSRS log files to see what the exact error message is?

The files, depending on your version of SQL, should be named similarly to "ReportServerService__08_06_2015_07_58_40.log". The default folder is "C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles"

I looked in the log files and could not find any match between the pasted error and anything in the log file. I did find some interesting snippets, however:

library!DefaultDomain!8cc!08/20/2015-10:16:41:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Cannot open database "ReportServer" requested by the login. The login failed.
Login failed for user 'NT SERVICE\ReportServer'.

August 20th, 2015 2:18pm

I'm getting a better handle on the Report Manager.

I right-clicked on the project and selected "manage".

Under "cache refresh options" it says: "There are no items to show in this view. Click Help for more information about this page."

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 2:34pm

That error doesn't have much to do with the error from your report. Once the RDL definition has been pulled from the RS db it is read and then processed. You got this error while the report was being processed so the connection to the report server database would have happened long before hand. Though I do find this strange.

Are the databases on the same instance as the ReportServer and ReportServerTempDB databases?

When the issue is happening, are you able to run the report from the reportserver url?

http://servername/reportserver

or 

http://localhost/reportserver

Is this a stand alone install or sharepoint integrated?

Have you patched SSRS up to the latest build?

August 20th, 2015 5:33pm

All good questions. 

I have not attempted to use the "localhost" form of the URL, as none of my other users are permitted login access to that server.

There is no interaction with Sharepoint -- I'm not sure if it's even installed or configured. The URLs we use are taken from the report server `http://london:4892/ReportServer`.

We do weekly updates through Windows Update, and I reverified to make sure we're current.

Someone else on my team went into Management Studio and explicitly gave `NT Serivce\ReportServer` all the database roles except for the two db_deny roles. We'll see tomorrow if this helps or not. At the very least it might take care of some of the interesting errors in the log.

I did the same thing to ReportServerTempDB just now. I never even noticed that DB (and I tend to leave things alone if I have no idea what they are).

Here's hoping this improves tom

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 5:50pm

So much for hoping. The error is appearing again.

It makes no difference whether I use localhost in the URL bar (when logged into the server) or not.

August 20th, 2015 6:19pm

Hi hatessunlight,

In your scenario, if you are using the embedded data source, please open the Data Sources page for the report in report manager, make sure you have specified the stored credential. If the account is a Windows domain user account, please select Use as Windows credentials when connecting to the data source. Then please click the Test Connection button to make sure connection is successful then click apply. For more information, please refer to similar thread below:
Getting a "An error has occurred during report processing. (rsProcessingAborted)"

If issue persists, please open the report with click-once report builder from the report manager, then run the query in query designer to check if results can be returned correctly. Then run the report in report builder.

Besides, since the error message indicates there is an issue during executing the query, you can take use of SQL Server Profiler to create a trace to trace the relevant event information related to your database server when you execute this report. For more information about this tool, please refer to this article: SQL Server Profiler.

If methods posted above dont help, as Daniel suggested, please check the Reporting Services error log (default location: %programfiles%\Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles) around the time that the error message thrown out. Please post the detail error log for our analysis if possible.

Reference:
Store Credentials in a Reporting Services Data Source

If you have any question, please feel free to ask.

Best regards,
Qiu

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 2:38am

I independently found the items you list and they have not helped me.

August 21st, 2015 10:28am

So much for hoping. The error is appearing again.

It makes no difference whether I use localhost in the URL bar (when logged into the server) o

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 10:32am

Doing a sort by date, I can see that the times coincide with when I deployed the projects. I don't see any mystery dates.

In any event, I don't use Sharepoint.

August 21st, 2015 10:43am

Doing a sort by date, I can see that the times coincide with when I deployed the projects. I don't see any mystery dates.

In any event, I don't use Share

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 11:19am

I'm the only developer, so there's no possibility of cross-contamination.  Looking at the results, I don't see anything that doesn't belong.

I have the feeling that the error message that started this conversation might be a red herring.

I can deploy a project and the report works great for everyone. Then randomly, it doesn't.  There must be something else going on. 

The fact that I've run my pages and queries in this fashion without any problem until this last week makes me strongly suggest I'm experiencing a bug that could have been introduced in a Windows or SQL update.

August 21st, 2015 11:27am

I'm the only developer, so there's no possibility of cross-contamination.  Looking at the results, I don't see anything that doesn't belong.

I have the feeling that the error message that started this conversation might be a red herring.

I can deploy a project and the report works great for everyone. Then randomly, it doesn't.  There must be something else going on. 

The fact that I've run my pages and queries in this fashion without any problem until this last week makes me strongly suggest I'm experiencing a bug that could have been introduced in a Windows or SQL u

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 12:18pm

There's something else that could be contributing: the use of shared datasets.

I'm converting them to embedded queries, and things seem to be working so far. I've been seeing in a few other forum posts that use of shared data sets can cause problems. 

I've changed a few of my projects, so I can see whether this makes a difference.

August 21st, 2015 12:27pm

I've run out of time to solve this problem and need to go to a plan B. Because SSRS has become unreliable, I'm doing my presentation in my MVC site. It's a little bit like using a sledgehammer to pound in a thumb tack, but at least I know that it will always run in the manner intended. Maybe this conversation might be useful to anyone else with this problem.
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 4:33pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics