CRMScript - Scheduled task fails

RE: CRMScript - Scheduled task fails

Hi Kim,

what happens if you run the script without a scheduled task? Does it time out?

Do you know how many rows it manages to update before stopping? Is it the same amount each time?

The script seems fine to me, so it doesn't seem like you're doing anything wrong, except maybe checking if the extra fields can contain letters, but that would've caused a different error message.

RE: CRMScript - Scheduled task fails

Hi Simen!

Thanks for you fast response :)
If I run the script with top 1000 rows, there are no issues.

The script runs through app. 16 million records in the first run. After the first run the script will be limited by the "lastRun" parameter. Could this be a problem?

I haven't been able to log the number of records. Strangely, the log()-function doesn't output anything in the Debug log :/
Probably because there are too many records to handle?

If I put a log before the for-loop, the log works fine.

But according to the database, there are not letters in the respective columns.

/Kim

RE: CRMScript - Scheduled task fails

Hi,

I am not sure why your script is failing, but I would like to point out a couple of things:

1. I have never heard of NetServer crashing and restarting because you ask it for too much data. 
2. From what I can see, you are bypassing NetServer in your query, right? Then it is kinda strange that you get an NetServer exception.
3. The log system does not stop logging because you are logging too much data either.

I would work more on the logging part. Also, you could create a trace for the script and see what you get there. 

Sverre

RE: CRMScript - Scheduled task fails

Hi Sverre,

Thanks for your reply.
If I insert a se.setLimit(1000) the script outputs logs in the Debug log.

However, if I remove setLimit, the script no longer logs :(
Does the SearchEngine calculate/prepare the query before running the for-loop?`

My concern is that the SearchEngine prepares all the records before entering the for-loop and since there are over 16 million records, it might take too long?

/Kim

RE: CRMScript - Scheduled task fails

What happens if you only run this code?

#setLanguageLevel 3;
 
/*SearchEngine st;
st.addField("schedule.last_execution");
st.addCriteria("schedule.id", "Equals", "3");
DateTime lastRun;
if(st.execute() > 0)
  lastRun = st.getField(0).toDateTime();
 
if(lastRun.toString() == "")
  lastRun = getCurrentDateTime();*/
 
SearchEngine se;
se.bypassNetServer(true);
se.addField("y_potential.x_total_potential");
se.addField("y_potential.x_rest_potential");
se.addField("y_potential.x_ok_budget");
se.addField("y_potential.id");
//se.addCriteria("y_potential.last_changed", "Gte", lastRun.toString(), "And", 0);
 
for(se.execute(); !se.eof(); se.next())
{
  String id = se.getField("y_potential.id");
  log("Id: " + id);
}

Will it fail then?

Just curious as to when it fails. As you say yourself, 16 million may be the reason, but I have never seen a scheduled task fail just like that, even with a high number like that.

RE: CRMScript - Scheduled task fails

Hi Simen,

Tried your suggestion and it still fails :(
No logs either.

If I use setLimit(1000) the script runs and logs perfectly.

BTW, retrieving the records in MSSQL takes app. 13 min.

/Kim

RE: CRMScript - Scheduled task fails

Hi,

Try adding a log-line just before and after the execute. I am guessing that since you are asking for a lot of data, the process dies while in execute() and consequently you never get to the first iteration of the loop. If you query through NetServer, then there are limitations to execution time and amount of data sent, since we are using Soap (i.e. HTTP) to communicate with NS. However, with bypassNetServer(true) it should be an ODBC query with no timeout. Since you are actually getting an NS error, I am thinking that the bypassNetServer() is not working. Could you try enabled FailedRequestLogging in IIS, as well as detailed NetServer logging and see if you get something?

Sverre

RE: CRMScript - Scheduled task fails

Hi Sverre,

Thanks, I will try that.
Btw, found out that bypassNetServer didn't work, as you mentioned. Reg_id 235 in the Registry table was set to 0. It is now changed to 1 :)

The scheduled task hasn't failed so far, but I guess it will take a couple of hours before completion.
Hopefully it won't fail.

But it's weird that there are no logs either, when the scheduled task is running. Are the logs deactivated when running scheduled tasks?

/Kim

RE: CRMScript - Scheduled task fails

Your log() call is inside the loop. I believe your script failed before the loop, because the amount of data was too much to transfer over a single SOAP call. Consequently, nothing was logged.

Sverre

RE: CRMScript - Scheduled task fails

Ahh I see.

Tried limiting the amount of data with setLimit 1 million records. The script did return loggings although it took quite some hours to complete the task.

Is there a way to enable SOAP to allow this enormous amount of data? Or do you have a better solution?

It's a very important customer with over 100 users, so we have to make this work asap :(

/Kim

RE: CRMScript - Scheduled task fails

Hi,

You can configure the maximum size of a SOAP package in web.config somewhere. Also, if you are able to bypass NetServer, then SOAP packet size will not be an issue, since we would use ODBC directly.

However, I feel like these are not the right solutions. You would still be left with a huge query which would take a long time to execute, which would leave you vulnerable. If anything wrong happens in the query, you are left at square one. How about breaking up the data set by doing multiple queries with a limited id-range each time? Something like this:

Integer max = 32 * 1000 * 1000; // Improvement: use a query to get max
Integer chunkSize = 10000;
for (Integer i = 0; i * chunkSize <= max; i++)
{
  SearchEngine se;
  // Add your criteria
  se.addCriteria("y_potential.id", "Gte", Integer(i * chunkSize).toString());
  se.addCriteria("y_potential.id", "Lt", Integer((i + 1) * chunkSize).toString());
  // The rest of your stuff
}

This way you would get 10K rows at a time, which is more manageable. 

Sverre

RE: CRMScript - Scheduled task fails

Hi Sverre,

Great input!
I will try this out and get back to you if it works :)

/Kim