Maximum Records!?!

[Des Hayles]

I have an issue that is somewhat perplexing. In an ADP project with Acc2002
and SQL Server 2000 I have a view made from 3 tables, two views and a
function. A couple of these objects have more than 10000 records. The view
returns 740 records when the maximum records setting for the view in
datasheet view is 10000 but returns 793 when the maximum records setting is
0 (all). I have opened all of the objects in the view, in a datasheet view
and set the maximum records settings to 0. The defaultmaxrecords setting has
also been set to zero. The question then is why are different numbers of
records returned both below 1000 by simply changing the maximum records of
the view?
The view works as I expect it to in the query analyzer, but something seems
to be limiting the records returned to access even though the settings is
way above the expected return of records.

Des

 

[Sylvain Lafontaine]

Maybe a local or a server filter on the view?

 

[Des Hayles]

I checked for filters and there are none.
After reading the MS article Q283200 I'm left with some confusion as to how
ADP queries work. I understood that the maximum records settings for forms
and datasheet views of queries and tables only affect what data is returned.
Therefore regardless of the number of records in any object within the query
all records are processed but only 10000 (default) records are returned if
that is indeed the setting you have in the ADP. What I think I'm seeing in
my situation is that the maximum record setting for the view is used to
limit the number of records of each object in the view before processing, is
this possible?

Des

 

[Sylvain Lafontaine]

The effect of the maximum records settings is to add an instruction « SET
ROWCOUNT 10000 » before any sent select statement.

I don't know how this will affect the views that you are using but you can
take a look with the SQL Server profiler to learn more about this.

 

[Des Hayles]

Sylvain:

I've narrowed things down to this. The view I'm working with contains a
table and a function that returns data via a table variable. In the profiler
I can see that when I open the view in the ADP the set rowcount is inserted
at the beginning. My understanding is that this applies to the records being
RETURNED and not to the table or function used in the view. The problem
that I described in earlier messages in this thread is that changing the
maximum records from the datasheet view of the view yeilds two different
record counts both well below the 10000 that I have set. And, the default
maxrecord setting is at 0.

Any takers??

 

[Sylvain Lafontaine]

No, the ROWCOUNT doesn't applies only to the records being returned, it will
also affect all intermediary results, including triggers and functions.
This is a global option that will affect all statements (views, functions
and SPs) following its call.

If you want to limit only the number of records returned, you must a TOP N
clause instead.

 

[Des Hayles]

Thanks Sylvain.
If this is the case then the default setting of 10000 in ADPs is extremely
dangerous, because even though your final result set is less than 10000 you
would have no indication that all the data from the underlying queries have
been truncated to 10000 records.
I have some work to do!!!

Thanks Des