DECLARE CURSOR intermittently failing
Gerard
I have a trigger which contains a cursor such as the example below.
DECLARE cCur CURSOR FOR
select aFld from aTable where bFld = @aVar and cFld = bVar and dFld =
@cVar
this declare intermittently fails, on one occasion the declare
succesfully executes but then it can fail.
I have checked that the data is in the table once I get an error on the
client app that fires of the trigger for the select I declare the
cursor for.
in a previous situation removing some comments in my code has solved
this issue but I do not see how that can be related. Comments were
started with: --
anyone?
regards,
Gerard
Gerard
the cursor does not change this behaviour and that would return
multiple rows
Andrew J. Kelly
pseudo code for the trigger.
--
Andrew J. Kelly SQL MVP
"Gerard" <g.doe...@gmail.com> wrote in message
news:1135361319....@g43g2000cwa.googlegroups.com...
Gerard
@DocumentNumber and SOPTYPE = @DocumentType and DISTTYPE = 1
OPEN dSeq
FETCH NEXT FROM dSeq INTO @LSeq
WHILE @@FETCH_STATUS = 0
BEGIN
-- do some operations on other table based upon the SEQNUMBR
END
Mike John
the trigger code - is something happening prior to the declare, or is it a
locking problem (lock timeout perhaps) on the declare.
Without the full code and exact error message things get a bit hard!
Don't you just love Great Plains naming (un)conventions though!
Mike John
"Gerard" <g.doe...@gmail.com> wrote in message
news:1135362705....@g14g2000cwa.googlegroups.com...
Andrew J. Kelly
--
Andrew J. Kelly SQL MVP
"Gerard" <g.doe...@gmail.com> wrote in message
news:1135362705....@g14g2000cwa.googlegroups.com...
Gerard
have determined it is a locking issue.. and yes I love Great Plains ...
with a vengeance that is ..
happy holidays..
Gerard
David Portas
> Hi,
>
> I have a trigger which contains a cursor such as the example below.
>
Don't put cursors in triggers. Just don't. Updates are set based,
triggers should be too.
--
David Portas
SQL Server MVP
--
Gerard
I appreciate your feedback but is this not a deja vu?
In my case I don't have a lot of choice other then to use triggers and
cursors within these triggers, you can take my word on it.
Even though they are set based the application I'm programming against
does all of the updates important to me on a row by row basis.
So just to be sure I do a check in the beginning of the trigger to see
if there is only one row.. (and a hell of a lot of other checks for
that matter)
set @rwcnt = @@rowcount
if @rwcnt = 0 or @rwcnt > 1 return
anyways thanks and happy holidays to you too,
Gerard
Chris2
Gerard,
Try posting the entirety of the code involved (see
http://www.aspfag.com/5006), including full DDL for the tables and
the trigger, and sample data.
I'll bet someone will re-write it without the cursor for you.
Sincerely,
Chris O.
Hugo Kornelis
Hi Gerard,
If you already checked to make sure that only one row will be returned
by the cursor, then why put in in a cursor at all?
DECLARE MyCurs CURSOR
FOR SELECT Column
FROM something that returns exactly one row
OPEN MyCurs
FETCH NEXT FROM MyCurs INTO @Column
CLOSE MyCurs
DEALLOCATE MyCurs
is exactly equivalent to
SELECT @Column = Column
FROM something that returns exactly one row
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
David Portas
> David,
>
> I appreciate your feedback but is this not a deja vu?
>
> In my case I don't have a lot of choice other then to use triggers and
> cursors within these triggers, you can take my word on it.
>
Elaborate please. Are you saying this is a management constraint ("the
boss won't let me") or a technical one?
> Even though they are set based the application I'm programming against
> does all of the updates important to me on a row by row basis.
>
That doesn't mean you need to use a cursor. It's still better not to.
> So just to be sure I do a check in the beginning of the trigger to see
> if there is only one row.. (and a hell of a lot of other checks for
> that matter)
>
> set @rwcnt = @@rowcount
> if @rwcnt = 0 or @rwcnt > 1 return
>
In that case you definitely don't need a cursor.
Gerard
It sounds rather factual that, how do you know? Just because I check at
the beginning of the trigger that there is only one row doesn't mean
that there can be no need for a cursor. If you had a look at my post
with the cursor code in it you should have noticed that the cursor is
not for inserted but for a regular table. And the cursor can contain
more then one row in my scenario, otherwise I wouldn't have used it.
What is bugging me a bit is that by stating that I shouldn't use a
cursor in a trigger or simply don't need one (even though there is no
reason to conclude that based on the info I provided in my post) you
sort of jump over the actual issue. Which is the fact that the cursor
intermittently fails. No I have been able to ascertain what the issue
is, see my post from Dec 23, 10:07 pm, so that's that sorted.
Your soundbites about not using cursors in triggers haven't helped much
in that though.
regards,
Gerard
Scott Morris
> the beginning of the trigger that there is only one row doesn't mean
> that there can be no need for a cursor. If you had a look at my post
> with the cursor code in it you should have noticed that the cursor is
If you guarantee that there is only one row, then there absolutely no need
to invoke the overhead and expense of a cursor.
If you had posted the entire trigger code, someone might have given you free
consulting work and conducted a targeted evaluation of the code in order to
determine if you **truly** need a trigger. The only code snippet you posted
was the trigger declaration and a non-functional loop. "do some operations
on other table ..." isn't particularly meaningful or definitive.
> What is bugging me a bit is that by stating that I shouldn't use a
> cursor in a trigger or simply don't need one (even though there is no
> reason to conclude that based on the info I provided in my post) you
You gave little information - and there was insufficient information to
determine that you needed a cursor. If you are going to use a cursor, you
need to justify its use. Why? Because the appropriate technique to use in
**good** tsql programming is to use a set-based approach. Certainly there
are times when an iterative approach is needed (or required). These are
rare and should only be chosen because a proper set-based approach is either
not possible or not "satisfactory".
> sort of jump over the actual issue. Which is the fact that the cursor
> intermittently fails. No I have been able to ascertain what the issue
> is, see my post from Dec 23, 10:07 pm, so that's that sorted.
Perhaps next time you will post the actual error message in its entirety.
Based on the snippet of code you did post, you might also want to
investigate appropriate error handling logic within tsql (limited as it is).
> Your soundbites about not using cursors in triggers haven't helped much
> in that though.
Then it would appear that you have learned little from this ordeal. But who
knows; you never posted the actual solution that you implemented.
http://www.catb.org/~esr/faqs/smart-questions.html
Razvan Socol
What error message do you get ? If it's "A cursor with the name 'cCur'
already exists." then I suggest you use a local cursor instead of a
global cursor (which is the default). Even if you don't get this error,
it is always a good idea to use the cursor with the lowest impact on
resources, for example:
DECLARE TheCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT ...
Razvan
Gerard
First of thanks for the elaborate reply. But just as David you seem to
have missed out on something crucial here.
The cursor is >>NOT<< for the >>inserted table<< but for another table,
see below:
I wrote:
>>not for inserted but for a regular table. And the cursor can contain
>>more then one row in my scenario, otherwise I wouldn't have used it.
On your other remarks;
> You gave little information - and there was insufficient information to
> determine that you needed a cursor. If you are going to use a cursor, you
> need to justify its use.
That's just it I don't think I need to justify the use of a cursor in
my trigger, or anywhere else for that matter, to anyone. That doesn't
mean to say that I don't take its expense into consideration, on the
contrary. You can also take my word on it that a cursor is required in
my code and if you don't want to do that then fine, that's not my
problem.
> If you had posted the entire trigger code, someone might have given you free
> consulting work and conducted a targeted evaluation of the code in order to
> determine if you **truly** need a trigger
Thanks for the offer but no thanks, I like my consultants a bit more
flexible and openminded. And the determination of: *if I need a
trigger*, is not a question of if, it is a requirement.
> Based on the snippet of code you did post, you might also want to
> investigate appropriate error handling logic within tsql (limited as it is).
Well that's a thought, this is something I, or someone else, can do
something with. It's at least it's a suggestion worth looking at. Not
soundbites like: *Don't put cursors in triggers. Just don't* and *In
that case you definitely don't need a cursor*
> Then it would appear that you have learned little from this ordeal.
Well I have learned quite a lot actually, first but foremost that some
posters may have a tendency to just shout out there mantras first and
read the posts from the person asking the question later. Second that
you stick out for each other in here, which I find a good thing. And
third that you should take the advise in here for what it's worth.
all the best,
Gerard
Gerard
thanks for the tips.
Gerard
David Portas
> Well I have learned quite a lot actually, first but foremost that some
> posters may have a tendency to just shout out there mantras first and
> read the posts from the person asking the question later. Second that
> you stick out for each other in here, which I find a good thing. And
> third that you should take the advise in here for what it's worth.
>
> all the best,
>
> Gerard
I hope you've also learned that posting working code to reproduce a
problem, together with DDL and a few rows of sample data helps avoid
misunderstandings and gets you faster answers. You could have provided
that in a fraction of the time it took you to reply to the parts of
this thread that you found less helpful!
Gerard
I'm sure everyone that contributed to this mini discussion learned
something, and yes, in future, I will do my utmost to avoid
misunderstandings.
Sometimes one needs to spend a bit of time to get results, and I think
I got the result I wanted.
all the best,
Gerard
Gerard
I had a good read through BOL on the matter and this definitly helps a
lot, I did not know about the LOCAL option and was accustomed to use
CLOSE and DEALLOCATE explicitly to avoid the cursor being created
twice.
Can you tell me if, when using a local cursor, it still is good
practice to close and deallocate the cursor explicity or leave it up to
SQL Server to do so?
regards,
Gerard
Raymond D'Anjou
Read and weep.
"Transact-SQL cursors are extremely efficient when contained in stored
procedures and triggers."
"The use of cursors in triggers is not recommended because of the
potentially negative impact on performance."
"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:1135418625.6...@g44g2000cwa.googlegroups.com...
David Portas
It's generally good practice to release resources as early as possible.
DEALLOCATE explicitly.
Gerard
is that both 2000 and 2005?
great to see that there are no truths though. I'd lost all confidence.
regards,
Gerard
Raymond D'Anjou
2000. I haven't touched 2005 yet.
I tend to agree with #2 and most of the SQL experts though.
Short triggers with no Cursors and no calls to Stored procedures.
In fact, the only cursors I use (3 of them) are in stored procedures that
are run once a week when no clients are using our site.
I could probably rewrite the procedures without the cursors but I'm lazy.
Gerard
which is to add row numbers to freshly inserted records , Sql Server
2005 ROW_NUMBER() will solve all of that I understand.
And gathering from earlier posts no set based solution can do that more
efficiently. This post in particular is what I'm referring to:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/b8320443418b6c2f?hl=en&
David Portas
In that post Itzik is making some general statements about a class of
problems that require row numbering. That generalization doesn't apply
to every example. There are set-based solutions for row numbering that
outperform cursors in given situations, especially for some sizes of
problem (larger sets are usually better handled by paging - although
you still won't need a cursor for that).
So as many others have been suggesting, you may be completely mistaken
about needing to use a cursor. Of course we can still only guess
because you still haven't posted the actual problem...