7 Replies Latest reply: May 16, 2011 4:17 AM by Peter Rieper RSS

    Dreaded "General Script Error"

      I changed a bit of code in a load script yesterday to handle a bug. I ran through a quick limited load test on 10 rows to verify my syntax (it worked) then pushed the code to my QC environment to do a full load. The script failed with a "General Script Error". I went back to my dev environment and ran more limited loads, increasing the order of magnitude from 10 to 100 to 1000 to 10,000 to 100,000. Each time the script ran successfully. So I let it run overnight to load the million plus records and came in to find that it failed. I'm assuming that it's a data issue of some sort, but I'm curious if anyone can tell me if there is something in the new code I introduced that is likely to trigger the error. Here's my code, with the new field bolded:

          LOAD

              *,

              if(PassAnnualFlag = ID, 'Y',

                     if(NoPassAnnualFlag = ID AND ISNULL(lookup('ID', 'PassAnnualFlag', ID)),

                     'Y',

                     'N')

              )

              as BestAnnualAttemptNew,

              if(PassAnnualAttempt = ID, 'Y', if(NoPassAnnualAttempt = ID, 'Y', 'N')) as BestAnnualAttempt,

              if(PassOverallAttempt = ID, 'Y', if(NoPassOverallAttempt = ID, 'Y', 'N')) as BestAttempt,

              if(PassLatestAttempt = ID, 'Y', if(AttemptCount > 4 and NoPassLatestAttempt = ID, 'Y', 'N')) as LastAttempt,

              MyMonth

              MyDate

          RESIDENT

              TestStatusLatest

          ORDER BY ID, PYSort, MyDate desc;

       

      I haven't used the ISNULL operator much so I'm wondering if I need to handle that differently, i.e., test it's value for -1 or 0?

       

      For a bit of context, what I'm trying to do is look through a set of records (where ID is the "primary key" and there are N records per ID) to see if I have ever set the PassAnnualFlag. If I have not done so, I want to set the BestAnnualAttemptNew flag to 'Y' when the NoPassAnnualFlag contains the same value as the ID for that record. If I have set the PassAnnualFlag, I do not want to set the BestAnnualAttemptNew flag to 'Y' even if the NoPassAnnualFlag matches the ID for that record.

       

      Thanks in advance for any assistance.

      Jim

        • Dreaded "General Script Error"
          Erich Shiino

          Since you ran it with 100,000 records and it worked but it failed with millions, I would check how much RAM memory you have available on the server, maybe you reached the limit.

            • Dreaded "General Script Error"

              Thanks, but I'm sure that's not the issue. I've run larger loads successfully and ran the load for this app earlier in the day (before my code change) without error.

                • Dreaded "General Script Error"

                  It's pretty clear to me that it's a data issue. I have found that it fails at a particular spot (can't identify the exact record, but it's somewhere around the 262,000th record). Does anyone have a good way of debugging a data issue? I'd like to be able to output the record that the load script chokes on so I can inspect it, but I am not sure how to do that.

                    • Dreaded "General Script Error"
                      Peter Rieper

                      Do you create cartesian products, synthetic keys?

                      Have you watched the Taskmanager - consumption of RAM during the execution?

                      Does it help to skip one or more of the IF's?

                      When running on 64bit-machine you rather should replace the ISNULL-question with LEN(TRIM(Expression)) = 0.

                       

                      HTH

                      Peter

                        • Dreaded "General Script Error"

                          Apparently memory was the issue, or something like it. I was able to run the same code on a variety of data sets and it seemed as though it would only crash if I was loading >260,000 records. Loading different portions of the record sets (e.g., loading records 200,000 - 300,000) did not cause an error, so it's not a data issue. When I got to certain row counts, it would fail consistently.

                           

                          I switched from a Lookup function to using an Exists and did not encounter errors when running the full load. I'm still fighting with the logic I need to get to my desired result, but I think I'm past the errors that were plaguing me for now.

                        • Dreaded "General Script Error"
                          Daniel Rozental

                          Where's the information coming from? a text file?

                           

                          I would suggest installing a text editor (Textpad, Ultraedit, etc.) and trimming the file until you hit the problematic record.