HELP! "could not complete cursor operation because the set options have changed since the cursor was declared"
in [Programming SQL]
|
Prev: Msg 226, Level 16, State 6, Line 4 ALTER DATABASE statement not allowed within multi-statement transaction.
Next: The context connection is already in use (SQLCLR)
From: Stewart McGuire on 19 May 2006 16:32 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 using 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 also 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 name can change and those parts of a sql statement can NOT be parameterized (as far as I have been able to determine). |