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