June 27, 2012 at 5:25 am
Hi,
I have encountered a weird error while using sql server reporting services 2008r2.
i tried to schedule 20 reports at the same time. only 1 report is successfully executed. the rest of the reports encountered the following error
"the report server cannot open a connection to the report server server database. a connection to the database is required for all requests and processing"
but when i scheduled 20 reports at different timing, all the reports can be executed successfully
the rport database that i want to connect is on sql server 2008r2 failover clustering
can anyone advise me on how to troubleshoot this issue.
thank you
June 28, 2012 at 12:01 pm
When you run subscriptions at the same time, all those queries (from every dataset in every report) will be sent to your SQL server.
SQL server then runs all these queries at once. If one query ties up a table, it will block another connection. This connection stays in wait until whatever is blocking it either frees up the table or until your report times out (a setting in your reporserver config table). This is when you will get your error.
It is best practice to run your subscriptions on different times when possible. It is also best practice to run your subscriptions on off hours if possible to avoid user contention.
June 29, 2012 at 3:36 am
Hi,
i tried to schedule all the reports on the same time in my development server .
all the reports can be executed successfully at the same time
but when I tried it on the production server, only 1 report can be executed successfully.
the difference between production environment and development environment is
in the production environment, the administrator has applied system hardening on the production server.
he set some policies. I do not know whether the system hardening has cause this problem
thank you
July 20, 2012 at 5:04 pm
This may be a config issue. Can you ask your admin to give you a copy of the config file so you can compare?
Its probably best to review all differences but two that strike me are: MaxActiveReqForOneUser
and MaxQueueThreads.
You didn't say if you were using a data-driven subscription but if you are, it may be that the request is timing out.
Here is a link to MSDN article about the report server config file: http://msdn.microsoft.com/en-us/library/ms157273.aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply