Server-side FileMaker script randomly sticks on "Running"

[Mr. Scott]

In our FileMaker 11 solution, I created a script to update totals in a table. Rather than have that script manually-invoked by a user, I decided to have the FileMaker Server Advanced run the script every 2 minutes.

The script is not really complex:

The script options on the server are a 1-minute time limit set to abort schedule if time limit is reached or the server is stopped.

What is occurring at least one or more times throughout the day, is that the totals stop updating. A review in the Admin Console shows that the script's last completion was more than 2 minutes ago, and it's status is "Running". Without restarting the server, I have to delete the script and set it up within Admin Console again.

Any advice on why this is happening, and what I can do to prevent it?

Thanks in advance,

- - Scott

[Agnes Riley]

Hi Scott,

Have you tested the script in the debugger? The reason you should use error capture is so you can log the error. Otherwise you will never know what the error is on the server, since it has no GUI. Log the error(s) in a log table, because that's the only way you can tell why your script is hanging.

Also, 2 minutes intervals might not be enough. How long does it take it to run on FMA?

Best,

Agi

[Mr. Scott]

Hi, Agnes…thanks for responding:

The only error is when Get (FoundCount) = 0, which returns a 401 error. The script takes less than 30 seconds to perform across the WAN from a client computer, but only 10-12 seconds when the Server runs the script on a scheduled basis.

- - Scott

BTW: I added an "Exit Script []" step at the very end. Maybe that will do the trick? I wonder though…Do I need to put a value in the brackets, such as "False", or "True"?

[Vaughan]

I'd not be using the Replace command. Instead, loop through the found set and use Set Field on each record.

[Agnes Riley]

You could put a value in the exit script script step. If I were you I'd put Get ( LastError ) or a variable with that information.

401 is "No records match the request". So your script cannot find the records. I would do error checking after finding records, and if there's an error, report and bail.

I know it's not easy to troubleshoot scripts run from the server, and the only way to do it is by checking for errors, and making educated decisions as to what to do with an erroneous step.

And I agree with Vaughan's suggestion.

[Mr. Scott]

OK, Agnes and Vaughan:

The whole reason for this approach was due to the fact that I have a report in list-view that uses a self-join table to calculate instances of cases that share a certain issue. The Self-Join produces unstored calculations, which really slow this report down over the WAN, and more so with VPN over Wi-Fi.

So, I created a number field to reference instead of the unstored calculation. The server checks for record changes that would require updates to the totals, then replaces the values in only those records — the number field is really a temp field, so I'm not worried about a replace command affecting it.

However, I have followed you advice and revised the script with Set Field instead of "Replace", as shown below. I hope that fixes this problem of runaway script on the Server.

Thanks for responding — both of you!

Best regards,

- - Scott Macdonald

[Agnes Riley]

One more comment: it is much safer to do a count before you loop, set a counter and increment it when you loop, instead of going to the next record and exiting last.

Here's an example:

[Mr. Scott]

Cool, Agnes. I appreciate your feedback, and will check this out more thoroughly tonight. By the way, why is it "safer"? ALso, Why the "Set Field" Loop vs. Calculated "Replace"?

- - Scott

[Vaughan]

One more comment: it is much safer to do a count before you loop, set a counter and increment it when you loop, instead of going to the next record and exiting last.

I've never heard of that before. Nor have I ever encountered a problem with exit after last.

The replace is not a good choice because there is no way to trap for locked records, and it's also not server-side compatible is the dialog shows. Also the replace calculation is not explicitly visible so the code is harder to read and maintenance is harder. Replace is more for user interaction than scripting IMHO.

[Mr. Scott]

Ah…Good point, Vaughan:

So, how would I trap for locked records?

Doesn't matter at this point, as I am still finding that the script gets stuck in "Running". Since I don't have full Admin Console privileges, I can only delete and create it again. I wish I could kill scripts, and I know FMS12 separated the Script engine so that part of the server can be restarted…but alas, I am still on FMS11.

Seems like I'm always asking questions that never get answered, but generate other good questions!

Thanks again Vaughan,

- - Scott

[Wim Decorte]

The cleanest way to trap for locked recrods is to use an explicit "open record" script step before any set fields and trap for an error immediately after the "open record".

As to your original question: there is no good explanation why scripts hang on FMS10 and 11. It seems to be an issue with the product more than anything else. I don't see it often though. Server 12 lets us handle the issue more gracefully since server-side scripting now runs in its own process away from the database engine. So in 12 you can restart the server-side scripting engine without affecting your users.

[Steven H. Blackwell]

Those of you coming to the FileMaker DevCon in Miami Beach in a couple of weeks should be sure to attend Wim Decorte's session on FileMaker® Server 12 to learn more about all these changes.

Steven

[Mr. Scott]

Thank you, Mr. Decorte and Mr. Blackwell:

Doesn't look like I'll make it across the continent to this DevCon, but it's not for lack of seeing the value or the desire to attend. I do thank you for the suggestion on how to trap for record-locking during this script step — I have seen error 301 appear occasionally, but it's not something I was worried about due to the frequency of the Server-side script schedule (which hasn't bombed today, knock on wood!).

- - Scott