Jeff, I would capture the results of that dynamic query into text using cfsavecontent and then look through the result code to see if it parses correctly. If it parses correctly, meaning that the code is syntactically correct, I would copy and paste the result code into your SQL Server Management Assistant or whatever SQL Server tool that you use and see if the statement executes.
The code maybe be generating bad code. Also, you probably want to add some security to that dynamic statement using cfqueryparam for fields like #form.folder_ka_name#..etc. You could get SQL Injection possible cases unless you validate prior to executing this query. Teddy R. Payne, ACCFD Google Talk - [email protected] 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 >>> >> >> >
