|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
HELP! "could not complete cursor operation because the set options have changed since the cursor wasit 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). 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 Show quote "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 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). > > > 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 Show quote "John Bell" <jbellnewspo***@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: > >> 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). >> >> >>  |
|||||||||||||||||||||||
Other interesting topics