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