Multi-user issues with SSRS2000

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 you suggest how we can get around these errors.

Regards,

Martin

Because your large report is consuming so much memory during the rendering process, your worker process is probably recycling because it's hitting the max MemoryLimit as defined in machine.config.

The "Login Failed" error message that you mentioned is a symptom of your worker process recycling, coming back up, and then trying to request a report on your behalf...however, because it cycled, it lost your user context (credentials), ergo the "Null" user...

The article below discusses dealing with memory limits / issues with large reports.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;909678

sql