Multi-user errors

Hi,
At our company we are looking at implementing SQL Server 2000 Reporting
Services into our internet application. Having followed your book, and
produced a dozen reports, I have found a problem with calling reports
in a multi-user environment.
Some of the reports that our customers want can run to over 100 pages
which, whilst a large number, is a requirement. I have found that when
two people call one of these large reports at the same time; one of the
users gets the error "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection". Once the worker
process begins to render the report on the first user's screen, the
report may be refreshed on the second users screen and it renders quite
normally. Under normal circumstances the login credentials are valid
and identical for both users.
Furthermore, an error 'Server Application Unavailable' is often
generated when a number of users are requesting large reports this
appears to be caused by the worker process being recycled.
Our production servers are a mixture of Windows 2000 Server (SP4) and
Windows 2003 Server Standard Edition (SP1), the version of SQL Server
and Report services is 2000 Enterprise Edition, using the latest
service packs (SQL Server is SP4, and report services is SP2).
Can anyone suggest how we can get around these errors.
Regards,
MartinMartin,
It sounds like the problems occur during the processing/collecting of
data and that once that finishes and the reports begin rendering the
conflicts resolve. The first thing that comes to mind is rendering
these reports from snapshot data. Using a snapshot, you schedule the
query that generates your large dataset to run during off peak hours.
The reports can then be rendered immediately from the (already)
collected data rather than both collecting and rendering. This of
course assumes that you don't need up-to-the-moment i.e. realtime data
for the reports otherwise snapshots may not be an option for you.
Another option might be using subscriptions to distribute (push)
multiple copies of the reports rendered from one run of the process.
This might be the way to go if this an instance of one report being
called (pulled) by several users. Again, this assumes relatively
static data.
HTH
toolman
MartinClayton wrote:
> Hi,
> At our company we are looking at implementing SQL Server 2000 Reporting
> Services into our internet application. Having followed your book, and
> produced a dozen reports, I have found a problem with calling reports
> in a multi-user environment.
> Some of the reports that our customers want can run to over 100 pages
> which, whilst a large number, is a requirement. I have found that when
> two people call one of these large reports at the same time; one of the
> users gets the error "Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection". Once the worker
> process begins to render the report on the first user's screen, the
> report may be refreshed on the second users screen and it renders quite
> normally. Under normal circumstances the login credentials are valid
> and identical for both users.
> Furthermore, an error 'Server Application Unavailable' is often
> generated when a number of users are requesting large reports this
> appears to be caused by the worker process being recycled.
> Our production servers are a mixture of Windows 2000 Server (SP4) and
> Windows 2003 Server Standard Edition (SP1), the version of SQL Server
> and Report services is 2000 Enterprise Edition, using the latest
> service packs (SQL Server is SP4, and report services is SP2).
> Can anyone suggest how we can get around these errors.
> Regards,
> Martin