HELP! "could not complete cursor operation because the set options

Hi
Are you explicitly using a GLOBAL cursor if not have you checked
CURSOR_DEFAULT? Check out
http://msdn2.microsoft.com/en-us/library/ms189238.aspx
Have you thought about using dynamic SQL to populate a temporary table and
then using a cursor on that?
John
"Stewart McGuire" wrote:

> I am declaring a dynamic generated cursor in a nvarchar string and passing
> it to sp_executesql. I then want to open it and loop over the results usi
ng
> FETCH INTO. This worked fine in SQL 2000. In SQL 2005 I get the error
> message that I put in the header. It thinks that the SET OPTIONS have
> changed between my trigger and the scope of the sp_executesql stored
> procedure. I can NOT find ANY documentation anywhere that tells me WHAT
> options will cause this error so I can SET those options correctly in my
> trigger so they match whatever is getting set in the sp_executesql. I als
o
> can not locate any documentation as to what OPTIONS are being set in the
> sp_executesql stored procedure. I have not set any options explicitly in
my
> trigger, nor have I set any options to anything other than the defaults at
> the database level.
> Can someone help me please?
> --Stewart McGuire
> Malcolm Pirnie, Inc.
> P.S. I can't use a hard coded cursor because the column names and table na
me
> can change and those parts of a sql statement can NOT be parameterized (as
> far as I have been able to determine).
>
>John,
Thanks for that link. I was NOT using an explicit GLOBAL cursor. Turns out
that I was not able to pass a cursor parameter back out of my sp_executesql
call. It just did not like it. I had thought about using a temporary table
but I was unclear about the LOCAL scope. Turns out the LOCAL scope is not
just the currently executing trigger but the current connection/user so the
sp_executesql call has access to the temporary table that I declare in my
trigger. Then I just created a static cursor and opened that. Works like a
charm!
Thanks for your suggestions!
--Stewart
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8FED7AF7-4DC9-494C-991E-1D47CAA9F107@.microsoft.com...
> Hi
> Are you explicitly using a GLOBAL cursor if not have you checked
> CURSOR_DEFAULT? Check out
> http://msdn2.microsoft.com/en-us/library/ms189238.aspx
> Have you thought about using dynamic SQL to populate a temporary table and
> then using a cursor on that?
> John
> "Stewart McGuire" wrote:
>