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/> > ------------------------------------------------------------- >
