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:
>

Help writing a stored procedure...

I'm developing a library and want to display the alphabets across the
screen. When a user clicks on one of the alphabets I want all titles
beginning with that letter to appear on the screen.

How would I write this stored procedure?

My table is called Titles

Fields:
Title ID
Titles

Thanks!

The select statement migth need some improvements, I think that would result in a full table-scan. Have a look at you execution plan and optimize as required.

CREATE PROCEDURE st_Ret_MyStuff(@.characterClickedas varChar(1))ASBEGINSET NOCOUNT ON;SELECT yourColumnsFROM titlesWHEREleft(titleCol, 1)=@.characterClicked;ENDGO

Cheers!

/Eskil

|||

Thanks that works!