I did that and got the same error. I commented out the CURSOR FOR just to be sure that is not what you were talking about. Not sure what happened with the text format in that last email but I see in your reply that it is bold and huge. Apologies.
On Thu, Jan 20, 2011 at 4:50 PM, <[email protected]> wrote: > You're not wanting to comment out the CURSOR FOR...you want to comment > out the "SET NOCOUNT" and any other "SET" lines in that inner most nested > proc... > > ------------------------------ > *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Jeff > Howard > *Sent:* Thursday, January 20, 2011 4:44 PM > > *To:* [email protected] > *Subject:* Re: [ACFUG Discuss] help with where to start trouble shooting > an error > > same result and when commenting out the CURSOR FOR line I get > Error Executing Database Query. [Macromedia][SQLServer JDBC > Driver][SQLServer]The variable '@Folder_List_Cursor' does not currently have > a cursor allocated to it. > > On Thu, Jan 20, 2011 at 4:21 PM, <[email protected]> wrote: > >> For the time being to try it out - just comment out all the "SET" >> operations...try it out and see if it works...this isn't a great solution >> because the JDBC driver that you're using is real picky about sql server and >> the count reporting. It can crop up in weird ways...but just as a test, >> comment the sets for now...then let us know. >> >> ------------------------------ >> *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Jeff >> Howard >> *Sent:* Thursday, January 20, 2011 4:18 PM >> >> *To:* [email protected] >> *Subject:* Re: [ACFUG Discuss] help with where to start trouble shooting >> an error >> >> You are correct and make me feel like I'm getting closer to actually >> resolving this (thank you). I actually thought I was fairly proficient with >> SQL Server until trying to debug this. I tried removing the NOCOUNT and >> setting it to OFF but no luck. Here is the SP with the NOCOUNT: >> >> >> SET >> ANSI_NULLS ON >> >> GO >> >> SET >> QUOTED_IDENTIFIER ON >> >> GO >> >> ALTER >> PROCEDURE [dbo].[Folder_Child_List] >> >> ( >> >> @thisParent >> numeric(18,0), >> >> @Folder_List_Cursor >> CURSOR VARYING OUTPUT >> >> ) >> >> AS >> >> SET >> NOCOUNT ON >> >> SET >> @Folder_List_Cursor = CURSOR FOR >> >> SELECT >> F.Folder_Ka_ID >> >> FROM >> Folder_Ka F >> >> WHERE >> F.Soft_DELETE IS NULL >> >> AND F.Parent = @thisParent >> >> ORDER >> BY F.Priority_Display, F.Name >> >> -- Populate the cursor. >> >> OPEN >> @Folder_list_cursor >> >> RETURN >> >> >> On Thu, Jan 20, 2011 at 4:07 PM, <[email protected]> wrote: >> >>> Yup, that proc has a cursor in it - look at the FETCH statement.. >>> >>> More than likely it's this statement below: >>> >>> " >>> >>> EXECUTE >>> Folder_Child_List @Folder_KA_ID, @Cursor_Child_List OUTPUT >>> >>> " >>> >>> Looking at that, you've already declared and opened the cursor, and now >>> it's calling another stored proc. My hunch is that the proc >>> FOLDER_CHILD_LIST has a "SET NOCOUNT" in there that's messing the cursor >>> up... >>> >>> Check into that >>> >>> >>> >>> ------------------------------ >>> *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Jeff >>> Howard >>> *Sent:* Thursday, January 20, 2011 4:02 PM >>> *To:* [email protected] >>> *Subject:* Re: [ACFUG Discuss] help with where to start trouble shooting >>> an error >>> >>> No constraints or triggers on Folder_KA_Tree and here is the SP that >>> is called by the trigger: >>> >>> SET >>> ANSI_NULLS OFF >>> >>> GO >>> >>> SET >>> QUOTED_IDENTIFIER OFF >>> >>> GO >>> >>> ALTER >>> PROCEDURE [dbo].[Get_Next_Branch] >>> >>> ( >>> >>> @Folder_KA_ID >>> Int >>> >>> ) >>> >>> AS >>> >>> SET >>> NOCOUNT ON >>> >>> -- Add The Data Into The Return Table For This Record >>> >>> INSERT >>> INTO Folder_KA_Tree (Folder_KA_ID, Tree_Path) >>> >>> SELECT >>> F.Folder_Ka_ID, dbo.getFolderTreePath(@Folder_KA_ID) >>> >>> FROM >>> Folder_Ka F >>> >>> WHERE >>> F.Soft_DELETE IS NULL >>> >>> AND F.Folder_Ka_ID = @Folder_KA_ID >>> >>> DECLARE >>> @Cursor_Child_List CURSOR >>> >>> DECLARE >>> @thisFolder_ID numeric(18, 0) >>> >>> EXECUTE >>> Folder_Child_List @Folder_KA_ID, @Cursor_Child_List OUTPUT >>> >>> FETCH >>> NEXT FROM @Cursor_Child_List >>> >>> INTO >>> @thisFolder_ID >>> >>> WHILE >>> (@@FETCH_Status = 0) >>> >>> BEGIN >>> >>> -- Get Tree From This Folder Down >>> >>> EXECUTE Get_Next_Branch @thisFolder_ID >>> >>> FETCH NEXT FROM @Cursor_Child_List >>> >>> INTO @thisFolder_ID >>> >>> END >>> >>> -- Close the cursor. >>> >>> CLOSE >>> @Cursor_Child_List >>> >>> -- Deallocate the cursor. >>> >>> DEALLOCATE >>> @Cursor_Child_List >>> >>> SET >>> NOCOUNT OFF >>> >>> RETURN >>> Thoughts? >>> >>> On Thu, Jan 20, 2011 at 3:57 PM, <[email protected]> wrote: >>> >>>> Now you need to look at Folder_KA_Tree to see what triggers it has, >>>> and then take a look at the proc Get_Next_branch... >>>> >>>> ------------------------------ >>>> *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Jeff >>>> Howard >>>> *Sent:* Thursday, January 20, 2011 3:47 PM >>>> >>>> *To:* [email protected] >>>> *Subject:* Re: [ACFUG Discuss] help with where to start trouble >>>> shooting an error >>>> >>>> Ok, I'm in the db and there is one trigger and 5 constraints. I get >>>> the exact same error message when running the query in Mgmt Studio. Any >>>> tips on figuring out which of the 6 could be causing the issue? >>>> >>>> This is the trigger if it helps: >>>> >>>> SET >>>> ANSI_NULLS ON >>>> >>>> GO >>>> >>>> SET >>>> QUOTED_IDENTIFIER ON >>>> >>>> GO >>>> >>>> ALTER >>>> TRIGGER [dbo].[xxxxxx] ON [dbo].[Folder_Ka] FOR INSERT,UPDATE AS >>>> >>>> IF >>>> UPDATE ( Parent ) >>>> >>>> BEGIN >>>> >>>> DELETE FROM Folder_KA_Tree >>>> >>>> EXECUTE Get_Next_Branch @xxxxxxID = 0 >>>> >>>> END >>>> >>>> >>>> On Thu, Jan 20, 2011 at 3:30 PM, <[email protected]> wrote: >>>> >>>>> Try this out (if it's sql server 2005 or up) >>>>> >>>>> select * from sys.triggers >>>>> where parent_id = (select object_id from sys.objects where name = >>>>> 'folder_ka' and type = 'u') >>>>> >>>>> That would tell you if there's any triggers on the table (assuming you >>>>> have rights to query the sys schema). >>>>> >>>>> Allen >>>>> >>>>> ------------------------------ >>>>> *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Jeff >>>>> Howard >>>>> *Sent:* Thursday, January 20, 2011 3:02 PM >>>>> >>>>> *To:* [email protected] >>>>> *Subject:* Re: [ACFUG Discuss] help with where to start trouble >>>>> shooting an error >>>>> >>>>> Thanks. I'm working on getting the db credentials now to run the >>>>> exact query that is being displayed in the CF error message to test. Yes, >>>>> the code could use some help. This is behind some authentication but this >>>>> application is about 8 years old and if I started trying to bring it up to >>>>> best practices I'd end up rebuilding the entire app. >>>>> >>>>> On Thu, Jan 20, 2011 at 2:53 PM, <[email protected]> wrote: >>>>> >>>>>> More than likely it is a trigger or something along those lines >>>>>> because that's a vanilla insert statement below (that could use some >>>>>> help to >>>>>> avoid some sql injection). Take a look at the folder_ka table to see if >>>>>> there are any triggers/constraints on the table >>>>>> >>>>>> ------------------------------ >>>>>> *From:* [email protected] [mailto:[email protected]] *On Behalf Of *Steve >>>>>> Ross >>>>>> *Sent:* Thursday, January 20, 2011 2:49 PM >>>>>> *To:* [email protected] >>>>>> *Subject:* Re: [ACFUG Discuss] help with where to start trouble >>>>>> shooting an error >>>>>> >>>>>> Export the query to a string and run it in SQL Manager and see what >>>>>> it gives you >>>>>> >>>>>> On Thu, Jan 20, 2011 at 2:46 PM, Jeff Howard <[email protected]>wrote: >>>>>> >>>>>>> The code that is causing the issue is a simple insert query, >>>>>>> <cfquery name="new_folder_ka" datasource="#request.DSN#" > >>>>>>> insert into folder_ka >>>>>>> (name, Template_ID, Content_Sort_Order, description, created_by, >>>>>>> parent, levels, owner_id, show_disc_links, show_FinancialData_links, >>>>>>> show_InputSheet_links, create_date, last_update, >>>>>>> <cfif isDefined('form.timeframe') AND Len(Trim(form.timeframe))> >>>>>>> checkout_timeframe, >>>>>>> </cfif> >>>>>>> <cfif isDefined('form.check_email')> >>>>>>> checkin_email, >>>>>>> </cfif> >>>>>>> exclude_Search, exclude_myPage, Next_EmailAlert, >>>>>>> show_history_all) >>>>>>> values ( '#form.folder_ka_name#', >>>>>>> <cfif >>>>>>> isDefined('form.Template_ID')>#Val(form.Template_ID)#<CFELSE>NULL</CFIF>, >>>>>>> '#form.Content_Sort_Order#', '#variables.description#', >>>>>>> #variables.user_id#, >>>>>>> #Val(form.parent_id)#, >>>>>>> #variables.this_level#,#form.content_manager#, >>>>>>> <CFIF isDefined('form.show_disc_links')>1<CFELSE>0</CFIF>, >>>>>>> <CFIF >>>>>>> isDefined('form.show_FinancialData_links')>1<CFELSE>0</CFIF>, >>>>>>> <CFIF isDefined('form.show_InputSheet_links')>1<CFELSE>0</CFIF>, >>>>>>> getdate(), getdate() >>>>>>> <cfif isDefined('form.timeframe') AND Len(Trim(form.timeframe))> >>>>>>> , #form.timeframe# >>>>>>> </cfif> >>>>>>> <cfif isDefined('form.check_email') AND >>>>>>> Len(Trim(form.check_email))> >>>>>>> , #form.check_email# >>>>>>> </cfif> >>>>>>> ,<CFIF isDefined('form.exclude_Search')>1<CFELSE>0</CFIF> >>>>>>> ,<CFIF isDefined('form.exclude_myPage')>1<CFELSE>0</CFIF> >>>>>>> ,<CFIF isDefined('form.Next_EmailAlert')>1<CFELSE>0</CFIF> >>>>>>> <cfif IsDefined("form.history")> >>>>>>> , 1) >>>>>>> <cfelse> >>>>>>> , 0) >>>>>>> </cfif> >>>>>>> </cfquery> >>>>>>> It doesn't appear that a stored proc is involved unless maybe there >>>>>>> is a trigger I'm unaware of. Thoughts? >>>>>>> >>>>>>> Thanks again, >>>>>>> Jeff >>>>>>> >>>>>>> On Thu, Jan 20, 2011 at 2:37 PM, Teddy R. Payne < >>>>>>> [email protected]> wrote: >>>>>>> >>>>>>>> Jeff, >>>>>>>> In the SQL Server realm, a cursor is typical of a stored procedure >>>>>>>> that has a loop structure. Without using while loops and table >>>>>>>> variables, >>>>>>>> cursors used to be the defacto way to loop over logic in complex stored >>>>>>>> procedures. >>>>>>>> >>>>>>>> Teddy R. Payne, ACCFD >>>>>>>> Google Talk - [email protected] >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Jan 20, 2011 at 2:34 PM, Jeff Howard <[email protected]>wrote: >>>>>>>> >>>>>>>>> Hey all, >>>>>>>>> >>>>>>>>> I've got a client that I do some maintenance work for on a VERY old >>>>>>>>> CF site. They have a CMS that is throwing an error when trying to add >>>>>>>>> content. The servers have changed, the databases have been updated, >>>>>>>>> the >>>>>>>>> system runs across 3 different servers so I'm not sure where along >>>>>>>>> the way >>>>>>>>> the system "broke" >>>>>>>>> >>>>>>>>> I am getting the following error message on a server running CFMX7 >>>>>>>>> and I believe MSSQL that was recently updated from 2003 to 2005 >>>>>>>>> (possibly >>>>>>>>> 2008): >>>>>>>>> >>>>>>>>> Error Executing Database Query. >>>>>>>>> [Macromedia][SQLServer JDBC Driver][SQLServer]Could not complete >>>>>>>>> cursor operation because the set options have changed since the >>>>>>>>> cursor was >>>>>>>>> declared. >>>>>>>>> >>>>>>>>> >>>>>>>>> Any ideas on where to begin with this and possible culprits? >>>>>>>>> >>>>>>>>> Thanks in advance, >>>>>>>>> Jeff >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Steve Ross >>>>>> web application & interface developer >>>>>> http://blog.stevensross.com >>>>>> [mobile] (912) 344-8113 >>>>>> [ AIM / Yahoo! : zeriumsteven ] [googleTalk : nowhiding ] >>>>>> >>>>>> ------------------------------------------------------------- >>>>>> To unsubscribe from this list, manage your profile @ >>>>>> http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform> >>>>>> >>>>>> For more info, see http://www.acfug.org/mailinglists >>>>>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >>>>>> List hosted by FusionLink <http://www.fusionlink.com/> >>>>>> ------------------------------------------------------------- >>>>> >>>>> >>>>> >>>>> ------------------------------------------------------------- >>>>> To unsubscribe from this list, manage your profile @ >>>>> http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform> >>>>> >>>>> For more info, see http://www.acfug.org/mailinglists >>>>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >>>>> List hosted by FusionLink <http://www.fusionlink.com/> >>>>> ------------------------------------------------------------- >>>>> >>>> >>>> >>>> ------------------------------------------------------------- >>>> To unsubscribe from this list, manage your profile @ >>>> http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform> >>>> >>>> For more info, see http://www.acfug.org/mailinglists >>>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >>>> List hosted by FusionLink <http://www.fusionlink.com/> >>>> ------------------------------------------------------------- >>>> >>> >>> >>> ------------------------------------------------------------- >>> To unsubscribe from this list, manage your profile @ >>> http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform> >>> >>> For more info, see http://www.acfug.org/mailinglists >>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >>> List hosted by FusionLink <http://www.fusionlink.com/> >>> ------------------------------------------------------------- >>> >> >> >> ------------------------------------------------------------- >> To unsubscribe from this list, manage your profile @ >> http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform> >> >> For more info, see http://www.acfug.org/mailinglists >> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >> List hosted by FusionLink <http://www.fusionlink.com/> >> ------------------------------------------------------------- >> > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform> > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com/> > ------------------------------------------------------------- >
