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

Multi-user issue

I have a medium sized application running on Windows Mobile 5.0 devices.
This app was developed in VS 2005 and uses the Microsoft application blocks
for data access - running a stored proc. This app has been in use for
several years and works well. I've added a class for the unloading of
containers from our overseas operations. This app works well when one user
is scanning cases, but additional users keep getting kicked out. The
original user never has any trouble. When monitoring the SQL profiler I see
the "Audit Logout" message 7 or 8 times each time a user is kicked out. All
other functionality is working fine, we also have nearly 100 pc's running .
Net apps against this database - all with no problems. This one class/stored
proc is the only one giving trouble. The only real difference with this one
is that I am using the "SaveNoTransaction" method instead of the "Save"
method in the application blocks. I've looked at the code in this method and
see no problem there either. Any suggestions? I'm running out of ideas.
Thanks
Hi
"RickBentley" wrote:

> I have a medium sized application running on Windows Mobile 5.0 devices.
> This app was developed in VS 2005 and uses the Microsoft application blocks
> for data access - running a stored proc. This app has been in use for
> several years and works well. I've added a class for the unloading of
> containers from our overseas operations. This app works well when one user
> is scanning cases, but additional users keep getting kicked out. The
> original user never has any trouble. When monitoring the SQL profiler I see
> the "Audit Logout" message 7 or 8 times each time a user is kicked out. All
> other functionality is working fine, we also have nearly 100 pc's running .
> Net apps against this database - all with no problems. This one class/stored
> proc is the only one giving trouble. The only real difference with this one
> is that I am using the "SaveNoTransaction" method instead of the "Save"
> method in the application blocks. I've looked at the code in this method and
> see no problem there either. Any suggestions? I'm running out of ideas.
> Thanks
>
With the audit logouts I would expect that the connection is deliberately
being closed. Have you got try-catch blocks in the code to trap errors? Have
you also looked at the Errors and Warnings exceptions in Profiler?
John

Multi-user issue

I have a medium sized application running on Windows Mobile 5.0 devices.
This app was developed in VS 2005 and uses the Microsoft application blocks
for data access - running a stored proc. This app has been in use for
several years and works well. I've added a class for the unloading of
containers from our overseas operations. This app works well when one user
is scanning cases, but additional users keep getting kicked out. The
original user never has any trouble. When monitoring the SQL profiler I see
the "Audit Logout" message 7 or 8 times each time a user is kicked out. All
other functionality is working fine, we also have nearly 100 pc's running .
Net apps against this database - all with no problems. This one class/stored
proc is the only one giving trouble. The only real difference with this one
is that I am using the "SaveNoTransaction" method instead of the "Save"
method in the application blocks. I've looked at the code in this method and
see no problem there either. Any suggestions? I'm running out of ideas.
ThanksHi
"RickBentley" wrote:
> I have a medium sized application running on Windows Mobile 5.0 devices.
> This app was developed in VS 2005 and uses the Microsoft application blocks
> for data access - running a stored proc. This app has been in use for
> several years and works well. I've added a class for the unloading of
> containers from our overseas operations. This app works well when one user
> is scanning cases, but additional users keep getting kicked out. The
> original user never has any trouble. When monitoring the SQL profiler I see
> the "Audit Logout" message 7 or 8 times each time a user is kicked out. All
> other functionality is working fine, we also have nearly 100 pc's running .
> Net apps against this database - all with no problems. This one class/stored
> proc is the only one giving trouble. The only real difference with this one
> is that I am using the "SaveNoTransaction" method instead of the "Save"
> method in the application blocks. I've looked at the code in this method and
> see no problem there either. Any suggestions? I'm running out of ideas.
> Thanks
>
With the audit logouts I would expect that the connection is deliberately
being closed. Have you got try-catch blocks in the code to trap errors? Have
you also looked at the Errors and Warnings exceptions in Profiler?
John

Multi-user issue

I have a medium sized application running on Windows Mobile 5.0 devices.
This app was developed in VS 2005 and uses the Microsoft application blocks
for data access - running a stored proc. This app has been in use for
several years and works well. I've added a class for the unloading of
containers from our overseas operations. This app works well when one user
is scanning cases, but additional users keep getting kicked out. The
original user never has any trouble. When monitoring the SQL profiler I see
the "Audit Logout" message 7 or 8 times each time a user is kicked out. All
other functionality is working fine, we also have nearly 100 pc's running .
Net apps against this database - all with no problems. This one class/store
d
proc is the only one giving trouble. The only real difference with this one
is that I am using the "SaveNoTransaction" method instead of the "Save"
method in the application blocks. I've looked at the code in this method an
d
see no problem there either. Any suggestions? I'm running out of ideas.
ThanksHi
"RickBentley" wrote:

> I have a medium sized application running on Windows Mobile 5.0 devices.
> This app was developed in VS 2005 and uses the Microsoft application block
s
> for data access - running a stored proc. This app has been in use for
> several years and works well. I've added a class for the unloading of
> containers from our overseas operations. This app works well when one use
r
> is scanning cases, but additional users keep getting kicked out. The
> original user never has any trouble. When monitoring the SQL profiler I s
ee
> the "Audit Logout" message 7 or 8 times each time a user is kicked out. A
ll
> other functionality is working fine, we also have nearly 100 pc's running
.
> Net apps against this database - all with no problems. This one class/sto
red
> proc is the only one giving trouble. The only real difference with this o
ne
> is that I am using the "SaveNoTransaction" method instead of the "Save"
> method in the application blocks. I've looked at the code in this method
and
> see no problem there either. Any suggestions? I'm running out of ideas.
> Thanks
>
With the audit logouts I would expect that the connection is deliberately
being closed. Have you got try-catch blocks in the code to trap errors? Have
you also looked at the Errors and Warnings exceptions in Profiler?
John

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

Multi-user environment

Hi,
I’m working on a project that has MSDE installed on a Server (Windows Small
Business Server 2003 Standard with Veritas Backup V.9.1) for the back-end
database and for the front-end an Access Project (.adp) which I plan to have
on each user's computer.
The Server Administrator is pushing for an update to SBS 2003 Premium with 5
licenses as the only solution. Personally I think MSDE would do the job…
There are few things I’m not so sure:
- How many users can access the server at the same time with MSDE?
- Is there a way to let the user know if a record is being used? Like
displaying a message before any changes are made?
- I’ve created a table for users with username, password and group they
belong to. Is there a way to integrate this with the MSDE Groups and
Permissions or Transact-SQL? So Users, passwords and Groups can be setup from
a form in the front-end.
I’m doing some research on Veritas and see if it would backup the Instance
of MSDE.
Any help on this matter would be greatly appreciated
gaba
hi,
gaba wrote:
> - How many users can access the server at the same time with MSDE?
it's not a matter of users but concurrent batches... a study by Microsoft
indicates a magic number of 25, but it really depends on the application
type/design, database type/design...
of course bad ADO serverside cursors design will be worse then excellent
clientside disconnected design code...
but actually there's no limit, just the buuilt-in Workloads Gevernor that
kicks in when more then 8 concurrent batches are in progress... you can have
a look at
http://msdn.microsoft.com/library/?u...asp?frame=true
fro further info about that governor...

> - Is there a way to let the user know if a record is being used? Like
> displaying a message before any changes are made?
actually not... you have to test saving and trap the relative exception...

> - I've created a table for users with username, password and group
> they belong to. Is there a way to integrate this with the MSDE Groups
> and Permissions or Transact-SQL? So Users, passwords and Groups can
> be setup from a form in the front-end.
I think you've better drop your user tables and rely on the standard SQL
Server users and roles...you are duplicating all and incurring in troubles..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks so much for your answer. I see I need to change directions in few
settings but I'm on the right track. A lot of information to catch up...
gaba
"gaba" wrote:

> Hi,
> I’m working on a project that has MSDE installed on a Server (Windows Small
> Business Server 2003 Standard with Veritas Backup V.9.1) for the back-end
> database and for the front-end an Access Project (.adp) which I plan to have
> on each user's computer.
> The Server Administrator is pushing for an update to SBS 2003 Premium with 5
> licenses as the only solution. Personally I think MSDE would do the job…
> There are few things I’m not so sure:
> - How many users can access the server at the same time with MSDE?
> - Is there a way to let the user know if a record is being used? Like
> displaying a message before any changes are made?
> - I’ve created a table for users with username, password and group they
> belong to. Is there a way to integrate this with the MSDE Groups and
> Permissions or Transact-SQL? So Users, passwords and Groups can be setup from
> a form in the front-end.
> I’m doing some research on Veritas and see if it would backup the Instance
> of MSDE.
> Any help on this matter would be greatly appreciated
> --
> gaba
sql

Multi-user concurrency

Does anyone have links to various strategies for handling multi-user concurrency issues when updating data in SQL Server (2000 or 2005), either in the database (stored procedures) or via code.

I've seen a couple such as:

Check individual user updated columns to see if the database columns have changed and if not apply the updated columns (either individual updates or constructing a dynamic SQL statement).

Check all the fields for any change and if no change, update all the fields.

Check a version field for change and if no change, update all the fields.

What I haven't seen is complete solutions such as for the first one, ensuring the record is locked and can't be changed while checking for concurrency and ultimately updating the data. For the second one, how to raise an error if there was a problem, or for the third one how to ensure the record is locked between the version check and the update.

Even pieces of solutions are welcome (locking records, testing for change, etc). Just want to compare and contrast various methods as I create an infrastructure for 2005.

Thanks,

LarryC

SQL Server itself can control such concurrency. If you just want to lock the data when updating, the default trancation isolation level 'READ COMMITTED' is enough, you can refer to:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp

Is this what you want?

|||

That might work in conjunction with some of the samples I've seen to ensure the record can't change between checks, thansk!. I was looking for end-to-end strategies that various people have used to implement multi-user concurrency in their apps/databases. I'm guessing this is a common problem with a number of solutions, some of which are likely to be better than others in various senarios.

Also in conjunction with the method for ensuring the database doesn't change between test and update, do you have any samples of how to handle the possible resulting errors or tips on how to reduce/elminate errors? Currently I'm planning on doing updats and deletes from the bottom up and for tables at the same general level applying updates/deletes alphabetically (hoping to eliminate blocking locks) but any insight into issues or examples of solutions would be greatly appreciated.