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

asked 13 Aug '14, 07:53

Paula%20M's gravatar image

Paula M
105349
accept rate: 0%


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();
permanent link

answered 26 Aug '14, 15:50

Chris%20Keating's gravatar image

Chris Keating
2.6k1647
accept rate: 27%

Comment Text Removed

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

(26 Aug '14, 21:17) Chris Keating

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."

permanent link

answered 03 Dec '14, 11:50

Paula%20M's gravatar image

Paula M
105349
accept rate: 0%

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.

permanent link

answered 13 Aug '14, 09:03

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.4k41733
accept rate: 22%

edited 13 Aug '14, 09:05

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

(13 Aug '14, 09:43) Paula M

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!

(13 Aug '14, 14:14) Paula M
Replies hidden

Hi Paula,

Are you performing DataTable.Load() operations?

(13 Aug '14, 14:54) Jeff Albion

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

(14 Aug '14, 06:17) Paula M

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.

(15 Aug '14, 13:27) PhilippeBert...

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.

permanent link

answered 19 Aug '14, 07:32

Tom%20Mangano's gravatar image

Tom Mangano
587182334
accept rate: 6%

1

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).

(19 Aug '14, 12:35) Tim McClements
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×132
×55

question asked: 13 Aug '14, 07:53

question was seen: 713 times

last updated: 03 Dec '14, 11:50