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, <[email protected]> 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:* [email protected] [mailto:[email protected]] *On Behalf Of *Jeff > Howard > *Sent:* Thursday, January 20, 2011 4:18 PM > > *To:* [email protected] > *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, <[email protected]> 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:* [email protected] [mailto:[email protected]] *On Behalf Of *Jeff >> Howard >> *Sent:* Thursday, January 20, 2011 4:02 PM >> *To:* [email protected] >> *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, <[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/> >>> ------------------------------------------------------------- >>> >> >> >> ------------------------------------------------------------- >> 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/> > ------------------------------------------------------------- >
