Error Could not generate asynchronous keyset. The cursor has been deallocated.

Hi -
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
END
Just a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold'?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.google.co m...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END
|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).
|||I can't say whether it is a bug or not. I was just guessing, based on the error messages and that it work on
some server but not on other, that the difference can be this configuration. You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug and open a case with MS PSS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.google.c om...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).