We are running into this issue with a remote site on which we are trying to "reset" synchronization. We have found that when this involves a large enough number of records (due to re-synchronizing all data from some point in the past), synchronization fails during the upload stage due to "too many temporary tables". We have found that when this happens, we can get around it by excluding the offending tables (or some large subset of their records) from synchronization. But as you may imagine, that is less than an ideal solution.
Both consolidated and remotes are running SQL Anywhere / Mobilink 22.214.171.1246 on Windows Server 2008 (64 bit). In the current case, that table upload is failing on has over 326,000 inserts in the upload. The error logged on the client side is (in French):
E. 2013-02-08 11:35:30. Erreur de serveur : Message : ODBC : [Sybase][ODBC Driver][SQL Anywhere]Trop de tables temporaires dans la connexion (Etat ODBC = HY000, Code d'erreur natif = -817). Nom de la table : cs_archive_narratives. Clé(s) primaire(s) : 4295677467
The only triggers on the table on the consolidated are those generated by Mobilink on creating the synchronization model.
Any insight or suggestions would be appreciated.
Thanks, Bob Leviton
asked 20 Feb '13, 09:34
I notice that the
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.
A colleague reminded me that statement-level triggers (such as those created by synchronization models) create temporary tables, one for
Note that since your
Unfortunately if you are using shadow tables to hold the timestamp column you need the the triggers.
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
If you have to use triggers, there are two less-performant options: