SQLE_TOO_MANY_TEMP_TABLES - random error

[Former Member]

Hi,

I am using UltraLite version 11.0.1.2960, C# .NET cf 3.5 on a Motorola 9500 handheld. Randomly during execution of my application the users get this error - SQLE_TOO_MANY_TEMP_TABLES on a normal select statement. I cannot see a pattern - different tables, different size tables...etc.

What could cause this and how can I fix it?

Thanks in advance! Paula

[Former Member]

To fix this issue, I added indexes to the tables in the remote database on the columns I use in the order by or group by clauses. I actually found it in the documentation (RTFM...right!)

"UltraLite temporary tables A temporary table is used by an access plan to store data during its execution in a transient or temporary work table. This table only exists while the access plan is being executed. Generally, temporary tables are used when subqueries need to be evaluated early in the access plan, or when intermediate results do not fit in the available memory. Data in a temporary table is held for a single connection only. Temporary tables consist of rows and columns. Each column carries a particular kind of information, such as a phone number or a name, while each row specifies a particular entry. You can avoid using temporary tables by using an index for the columns used in the ORDER BY or GROUP BY clauses."

[chris_keating]

There is a resource leak in ULIndexSchema that is being used by DataTable. This leak is generally seen if you are using DataTables multiple times in an application instance with the same database connection. The same issue exists if the application is directly using ULIndexSchema. Overtime, the SQLE_TOO_MANY_TEMP_TABLE error will be encountered if the connection is not closed. A fix is currently being investigated for v12 and later that will provide a method to release the resources used by ULIndexSchema.

A workaround is to isolate the connection used by the DataTable and release it as soon as the DataTable is no longer being used. For example, this code should avoid this issue:

    ULConnection conn =  new ULConnection( conn_parms );
    DataTable dt = new DataTable();
    ULCommand qry = new ULCommand( a_sql_qry, conn );
    ULDataReader dr = qry.ExecuteReader();
    dt.Load( dr );
    \\*...application use of DataTable ... *\\
    dr.Dispose();
    dt.Dispose();
    qry.Dispose();
    conn.Close();

[chris_keating]

This has now been fixed and will be available in 12.0.1 Build 4156 and later 16.0.0 Build 2004 and later

[philippefbertrand]

Since it is happening randomly, it is unlikely the result of one overly complicated query.

Frequently this is the result of not properly closing result sets. Review your code to ensure database resources are released in a timely manner and reduce the number of simultaneously open cursors you have.

[Former Member]

Thanks for the response! I'll try your suggestion.

[Former Member]

Related question - is there a way to see or query how many cursors are open? I've reviewed the code and it's really pretty standard stuff - I'm using an UltraLite data adapter to fill a .net data table so there's not a lot of database resources to manage. I added .Dispose() on the adapter but I'm not sure that's enough.

Thanks for any suggestions!

[jeff_albion]

Hi Paula,

Are you performing DataTable.Load() operations?

[Former Member]

No...only using the adapter to fill data tables.

[philippefbertrand]

SQL Anywhere 11 is pretty old. Can you post a code sample?

No, there is no API to check the number of open result sets.

[t1950]

We're a PB shop. Our app uses ASA (started with 7 currently using 16). we learned long ago to do a COMMIT after select statements to unlock rows and close cursors. I'm not sure if a COMMIT in an UltraLite db will do the same.

[former_SQLA_member1694876]

For UltraLite, COMMIT simply makes changes permanent. It has no effect in relation to select statements. Cursors are closed using the Close method (or equivalent).