Have u tried putting a try/catch block around the query to see if it errors doing the IN statement?
> -----Original Message----- > From: Terry Riley [mailto:[EMAIL PROTECTED] > Sent: 23 April 2004 14:40 > To: [EMAIL PROTECTED] > Subject: Re: [ cf-dev ] ValueList problem? > > > To Hell with it! > > Just in case there is some limitation, somewhere, on the > length of the > IN() list, I've decided to go the other route: > > <cfset todays_date = #CreateODBCDate(Now())#> > > <cftransaction> > <cfloop list="#user_update_list#" index="i"> > <cfquery name="foo_update" datasource="#request.DSN#"> > UPDATE userlist > SET update_request = #todays_date# > WHERE UID = #i# > </cfquery> > </cfloop> > </cftransaction> > > which actually seems to be only half as fast as doing it in > one hit using > the IN(#user_update_list#) construct! And that despite 8000+ db hits > instead of the one? > > > Anyone see any real problems with this? > > > As an aside - in testing the various options, I found that : > > <cfquery name="foo_update" datasource="#request.DSN#"> > UPDATE userlist > SET update_request = #CreateODBCDate(Now())# > WHERE UID IN (#user_update_list#) > </cfquery> > > took about 11 *minutes* to complete. Whereas: > > <cfset todays_date = #CreateODBCDate(Now())#> > <cfquery name="foo_update" datasource="#request.DSN#"> > UPDATE userlist > SET update_request = #todays_date# > WHERE UID IN (#user_update_list#) > </cfquery> > > took only 19 *seconds*. Beware, therefore, using > #CreateODBCDate()# within > such an update - evidently, the #CreateODBCDate# is processed > separately > for every row updated. Can't think of any other explanation..... > > Cheers > Terry > > ----------Original Message--------- > > > i don't think there's a problem with the size of a ValueList, but > > there > > might be an issue on how many values you put into an IN ( ) > clause in > > SQL. > > > > > > > > > > > > > > [EMAIL PROTECTED] (Terry Riley) > > 23/04/2004 11:28 > > Please respond to dev > > > > > > To: [EMAIL PROTECTED] > > cc: > > Subject: Re: [ cf-dev ] ValueList problem? > > > > Unfortunately, Duncan, I don't have (easy) access (someone > else's live > > server), but the original failure seems to have happened at > end March, > > so doubt if they had data that far back, even if I could! > > > > I'll ask, though... > > > > Cheers > > Terry > > > > --------Original Message--------- > > > > > any errors reported in application.log? > > > > > > > > > > > > > > > > > > > > > [EMAIL PROTECTED] (Terry Riley) > > > 23/04/2004 10:58 > > > Please respond to dev > > > > > > > > > To: [EMAIL PROTECTED] > > > cc: > > > Subject: [ cf-dev ] ValueList problem? > > > > > > In one of our apps (CF5), we send out a bulk email (well, > about 8000 > > > max) once in a while to encourage subscribers (job > seekers) to either > > > update their subscription preferences or remove > themselves from the > > > mailing list. > > > > > > At some future point, we check who has bothered to update, and if > > > they haven't within a certain period, we assume > non-interest (or even > > > non-delivery of the email) and delete them automagically using a > > > batch program. > > > > > > Part of the original mailing code is to create a valuelist of IDs > > > for > > > those requiring to be mailed, which, after CFMAIL has > done its bit, > > > is used to update a datetime field (SQL2K) with the day's > date (see > > > below). > > > > > > On our development server (XP) it works as advertised, even with a > > > valuelist containing 8000+ 4-digit IDs. However, on the > live server > > > (NT), this part of the code does not appear to have executed, > > > although the 8000 emails have been sent. > > > > > > Is there a limit to the size of a ValueList()? If not, anyone have > > > any possible explanation as to why this doesn't work? > > > > > > > > > <!--- initial query ---> > > > > > > <cfset user_update_list = #ValueList(get_users.UID)#> > > > > > > <!--- cfmail stuff ---> > > > > > > <cfquery name="foo_update" datasource="#request.DSN#"> > > > UPDATE userlist > > > SET update_request = #CreateODBCDate(Now())# > > > WHERE UID IN (#user_update_list#) </cfquery> > > > > > > > > > Cheers > > > Terry Riley > > > > > -- > These lists are syncronised with the CFDeveloper forum at > http://forum.cfdeveloper.co.uk/ > Archive: > http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > CFDeveloper Sponsors and contributors:- > *Hosting and support provided by CFMXhosting.co.uk* :: > *ActivePDF provided by activepdf.com* > *Forums provided by fusetalk.com* :: *ProWorkFlow > provided by proworkflow.com* > *Tutorials provided by helmguru.com* :: *Lists > hosted by gradwell.com* > > To unsubscribe, e-mail: [EMAIL PROTECTED] > -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED]
