New request is not allowed to start

Hi folks,
We have an Access front end that happily does all its reading, riting
and rithmetic onto a SQL Server 2005 BE.
There is a timer event on one of the user forms that polls to see if
the DBA wants to kick you out (a flag on a table that gets set by an
Administrators task, and if it is on, then users get a message).
This all works fine, unless the user is running one of the two stored
procedures that take a long while to execute (up to 60 seconds).
When that happens, if the execution time of the sp is longer than the
polling time interval (20 seconds), a conflict seems to occur, and we
get the message "New request is not allowed to start because it should
come with a valid transaction descriptor."
We have tried putting the statement SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED; into the stored proc, but it doesn't seem to have
helped.
Any ideas on how we can solve the problem?
Can we somehow run the procedure as a batch that is initiated by the
front-end, but doesn't need to maintain a connection with it?
We were required by the Powers That Be to open a single connection on
our front end for any DB access, rather than open a connection for
each form or process. Could this have something to do with it?
Any ideas gratefully received, Ray<ray@.aic.net.au> wrote in message
news:1194497708.231234.313780@.z24g2000prh.googlegroups.com...
> Hi folks,
> We have an Access front end that happily does all its reading, riting
> and rithmetic onto a SQL Server 2005 BE.
> There is a timer event on one of the user forms that polls to see if
> the DBA wants to kick you out (a flag on a table that gets set by an
> Administrators task, and if it is on, then users get a message).
> This all works fine, unless the user is running one of the two stored
> procedures that take a long while to execute (up to 60 seconds).
> When that happens, if the execution time of the sp is longer than the
> polling time interval (20 seconds), a conflict seems to occur, and we
> get the message "New request is not allowed to start because it should
> come with a valid transaction descriptor."
> We have tried putting the statement SET TRANSACTION ISOLATION LEVEL
> READ UNCOMMITTED; into the stored proc, but it doesn't seem to have
> helped.
> Any ideas on how we can solve the problem?
> Can we somehow run the procedure as a batch that is initiated by the
> front-end, but doesn't need to maintain a connection with it?
> We were required by the Powers That Be to open a single connection on
> our front end for any DB access, rather than open a connection for
> each form or process. Could this have something to do with it?
> Any ideas gratefully received, Ray
>
Have you considered using
ALTER DATABASE SET SINGLE_USER WITH
[
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
];
?
David Portas|||Well, no, I hadn't considered that because the problem can occur when
only one user is connected to the database.
I'm sure that the conflict is not between User A and User B.
As far as I can see (and our error trapping seems to confirm this),
the problem is that Stored Proc 1 executed by User A causes a conflict
with Stored Proc 2 also executed by User A (the polling process).
So thanks for your suggestion, but I am hoping to find a way to allow
the user to initiate and receive notifications from two separate
processes. Perhaps one needs to run independently somehow, just by
being initiated at the front end? Or do we need multiple connections
from the front end?
Thanks,
Ray|||Just for the record, in case anybody else encounters this, we opened a
separate connection in the Access front end and did the polling
through it, which resolved the problemo.