I notice that the INSERT statement in the log includes ON EXISTING UPDATE but synchronization models don't generate that clause. Also, temporary tables are only created by synchronization models if you have enabled conflict resolution for uploaded updates (not inserts), and only one or two are created per synchronized table when the synchronization model is deployed (not during synchronizations). So the problem likely isn't from what the sync model generates.

Perhaps some Overridden or User-defined script is causing the problem, by either directly or indirectly (eg. in a called stored procedure) creating temporary tables.

---Update---

A colleague reminded me that statement-level triggers (such as those created by synchronization models) create temporary tables, one for REFERENCING OLD and one for REFERENCING NEW, though those should be dropped at the end of the trigger. In case the sync model triggers are causing the problem, there are some workarounds (assuming you are not using shadow tables for timestamp downloads):

1. The insert triggers are only needed if deleted rows can be re-added. Otherwise you can drop those triggers (the ones named <table>_ins).
2. The update triggers (named <table>_upd) can be dropped if you change the last_modified column from DEFAULT CURRENT TIMESTAMP to DEFAULT TIMESTAMP. (FYI, this became the default for sync models in version 12.0.1.)

Note that since your upload_insert scripts have ON EXISTING UPDATE, uploaded inserts would fire update triggers instead of insert triggers for existing rows.

Unfortunately if you are using shadow tables to hold the timestamp column you need the the triggers.

---2nd update---

Updating to save digging through the comments: The problem seems to arrise because temporary tables created by statement-level triggers are dropped after the trigger, but temporary table IDs are held until a transaction commits so the limit could be reached with large uploads that use statement-level triggers.

Avoiding triggers is the best solution, since it should also improve performance. For example, with a SQL Anywhere consolidated database, you can use DEFAULT TIMESTAMP for your last-modified columns instead of needing an update trigger. (As of 12.0.1 that is the default for synchronization models.) Also, you don't need an insert trigger if you never insert rows with same primary key as previously deleted rows, or if you don't download deletes.

If you have to use triggers, there are two less-performant options:

• For SA remotes, use incremental uploads. Not available for UL remotes.

• Use row-level triggers. More expensive than statement level triggers unless using -s 1 MobiLink server option.