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> 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] *On Behalf Of *Jeff > Howard > *Sent:* Thursday, January 20, 2011 4:02 PM > *To:* 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> 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] *On Behalf Of *Jeff >> Howard >> *Sent:* Thursday, January 20, 2011 3:47 PM >> >> *To:* 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> 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] *On Behalf Of *Jeff >>> Howard >>> *Sent:* Thursday, January 20, 2011 3:02 PM >>> >>> *To:* 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> 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] *On Behalf Of *Steve >>>> Ross >>>> *Sent:* Thursday, January 20, 2011 2:49 PM >>>> *To:* 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> 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 >>>>> > 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 >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Thu, Jan 20, 2011 at 2:34 PM, Jeff Howard <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 >>>> [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/> > ------------------------------------------------------------- >
