openning cursor inside trigger works in sql2000 but not in 2005

Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.
It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -
|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:

> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>