5 Replies Latest reply on Apr 4, 2007 6:44 AM by insuractive

    schedule insert problem

    JohnGree Level 1
      Hi i have a scheduled task which runs the code below, i am having problems with it.

      what i need to do is

      1. get all records from appoint_table where the servertime is lessthan time now (which i am sure my query is ok).

      2. set a session TotalSMSLeft which gets the users credit left.

      3. if the users credit is GREATER THAN 0 then do this
      3a. send email
      3b. UPDATE appoint_table clear the server time
      3c. select all records that servertime is empty
      3d.insert into SMS_Records where servertime is empty
      3e. delete all records where servertime is empty.

      4. else delete all records where servertime is empty.

      maybe i have to much code for what i m trying to do?

      can some please help me with the best code to use

      many thanks
        • 1. Re: schedule insert problem
          mikeap Level 1
          Have you thought about using a stored procedure? That would simplify some of your code and put it at the database level.
          • 2. Re: schedule insert problem
            scooter5791 Level 1
            Please be more precise about the actual issue. Saying things like "I am having problems" is much too vague to be of any help. I have several scheduled tasks that run each day that take a long time to process and normally would just time out under the default time allowed to process a request. I am taking a wild guess that this may be your problem, and that your script may be simply stopping after the allotted time allowed to complete a request (check your admin settings - I think the default is 90 seconds)
            • 3. Re: schedule insert problem
              insuractive Level 3
              Just curious - why are you setting session variables in a scheduled task? I'm not sure the CF scheduler supports cookies or session state. Are you trying to use those variables on another page?

              Also, I second Mikeap's suggestion - a stored procedure will definitely help with your processing time and maintainability.
              • 4. schedule insert problem
                JohnGree Level 1
                ok i changed the code to what i have below, this now sort of works, where the delete is i need it to delete the row of the current loop, the problem i get now is that

                say there are 2 emails to loop over, they both get sent twice, so i think it is the delete query?

                would it be this?
                • 5. Re: schedule insert problem
                  insuractive Level 3
                  I believe it is getting sent out twice because your <cfmail> tag is inside a <cfloop> of your GetSchH1 query, but you also have your <cfmail> tag looping over the query (query="GetSchH1"). Remove the query="GetSchH1" from your <cfmail> tag and it should only send out 1 copy of each.

                  Also, I would perform your deletes outside of your loops - that way you only have to make 1 connection to the database to delete everything and you don't risk deleting the other records before you email them.