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: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Jeff Howard Sent: Thursday, January 20, 2011 4:44 PM To: discussion@acfug.org 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, <axunderw...@ups.com<mailto:axunderw...@ups.com>> 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: ad...@acfug.org<mailto:ad...@acfug.org> [mailto:ad...@acfug.org<mailto:ad...@acfug.org>] On Behalf Of Jeff Howard Sent: Thursday, January 20, 2011 4:18 PM To: discussion@acfug.org<mailto:discussion@acfug.org> 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, <axunderw...@ups.com<mailto:axunderw...@ups.com>> 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: ad...@acfug.org<mailto:ad...@acfug.org> [mailto:ad...@acfug.org<mailto:ad...@acfug.org>] On Behalf Of Jeff Howard Sent: Thursday, January 20, 2011 4:02 PM To: discussion@acfug.org<mailto:discussion@acfug.org> 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, <axunderw...@ups.com<mailto:axunderw...@ups.com>> 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: ad...@acfug.org<mailto:ad...@acfug.org> [mailto:ad...@acfug.org<mailto:ad...@acfug.org>] On Behalf Of Jeff Howard Sent: Thursday, January 20, 2011 3:47 PM To: discussion@acfug.org<mailto:discussion@acfug.org> 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, <axunderw...@ups.com<mailto:axunderw...@ups.com>> 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: ad...@acfug.org<mailto:ad...@acfug.org> [mailto:ad...@acfug.org<mailto:ad...@acfug.org>] On Behalf Of Jeff Howard Sent: Thursday, January 20, 2011 3:02 PM To: discussion@acfug.org<mailto:discussion@acfug.org> 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, <axunderw...@ups.com<mailto:axunderw...@ups.com>> 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: ad...@acfug.org<mailto:ad...@acfug.org> [mailto:ad...@acfug.org<mailto:ad...@acfug.org>] On Behalf Of Steve Ross Sent: Thursday, January 20, 2011 2:49 PM To: discussion@acfug.org<mailto:discussion@acfug.org> 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 <jeh...@gmail.com<mailto:jeh...@gmail.com>> 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 <teddyrpa...@gmail.com<mailto:teddyrpa...@gmail.com>> 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 - teddyrpa...@gmail.com<mailto:teddyrpa...@gmail.com> On Thu, Jan 20, 2011 at 2:34 PM, Jeff Howard <jeh...@gmail.com<mailto:jeh...@gmail.com>> 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<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 For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
