 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / August 2005Maximum Records!?!
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 Maybe a local or a server filter on the view?  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >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 [quoted text clipped - 11 lines] > > Des 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 > Maybe a local or a server filter on the view? > [quoted text clipped - 13 lines] >> >> Des 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF >I checked for filters and there are none. > After reading the MS article Q283200 I'm left with some confusion as to [quoted text clipped - 26 lines] >>> >>> Des 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?? > The effect of the maximum records settings is to add an instruction « SET > ROWCOUNT 10000 » before any sent select statement. [quoted text clipped - 32 lines] >>>> >>>> Des 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Sylvain: > [quoted text clipped - 46 lines] >>>>> >>>>> Des 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 > No, the ROWCOUNT doesn't applies only to the records being returned, it > will also affect all intermediary results, including triggers and [quoted text clipped - 54 lines] >>>>>> >>>>>> Des |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.