Repository.GetElementSet("",0) results in SQL err

[Geert Bellekens]

When I try to get all elements in my model using Repository.getElementSet("",0) I get an SQL server error: "The query processor ran out of internal resources... "
Our model is located on a  SQL server 2008 with 16GB of memory.
The t_object table contains about 45000 rows.
I guess the query used to retrieve these elements is not optimal and SQL server chokes on it.

[Geert Bellekens]

After sending the DBError.txt to support I got following answer:

Thank you for sending the DBError file. This issue is being
investigated and will hopefully be fixed in a future build.
Unfortunately however we cannot provide any estimated schedule at this
time.

As a temporary workaround, you might consider passing a SQL query
directly to the GetElementSet method to build this collection. For
example:

Set AllElements = Repository.GetElementSet("SELECT Object_ID FROM
t_object WHERE Object_Type NOT LIKE 'Package'",2)

Of course I was very happy to try the workaround, but unfortunately that resulted in the same error.
Looking at the DBError.txt it is clear why: The GetElementSet apparently creates exactly the same query when being called with an sql string.
I guess it takes the results of the first query and then uses these results to build its (way too long) list of id's.
I did however manage to get a working solution for my specific problem as I am only interested in all elements residing in the selected package.
Instead of "Select Object_ID from t_object...) I created a query that gets me the id's of only the elements in the given package (and all it's subpackages down to 10 levels).
This solution will work most of the times, unless the root package (model) is selected of course.

Geert

[Geert Bellekens]

In build 848 they have fixed the issue so that the passed SQL query is used as a subquery iso getting all the id's from the query and then passing them to the main query.
So iso

Code: [Select]

Select ... from t_object where Object_ID in (123,234,...way too long list of id's...)it now does

Code: [Select]

Select ... from t_object where Object_ID in (<query passed as parameter>)This fix was not documented in the release notes.

The downside of this approach is that you have to be carefull which query you pass to the method. When using a local EAP file you cannot pass a query containing unions as unions in subqueries are not allowed by MS Access.

Geert