David McAfee
davidmcafee at gmail.com
Tue Mar 12 12:22:23 CDT 2013
Off Topic, yet somewhat related tangent... I've been experience some weirdness in my ADPs (which are going away in the next version anyway). It was always my understanding that an ADP let SQL do all the work. I have found some very large stored procedures would have varying results when using temp tables. When we ran a trace, we found the sproc was being called via an RPC. As a test, I created an MDB and a pass through query and it would return the correct results. The numbers matched SSMS output. It really messed with my head. I wonder how many reports were returned incorrectly over the past few years. David On Tue, Mar 12, 2013 at 10:11 AM, John W Colby <jwcolby at gmail.com> wrote: > I am in the process of converting local queries to pass through queries. > In order to do that I have to go construct the queries in SSMS, get it > working, then pull the SQL back into Access, place it in the PTQ and save > it. Once I do that, the entire thing is run on SQL Server and only data > returned. > > If the query is written in Access then "selection" data will be shipped > just to decide what actual data needs to be pulled. In general Access > pulls both sides of joins and all fields in where clauses. That is used to > then ask for specific rows. > > John W. Colby > > Reality is what refuses to go away > when you do not believe in it > > > On 3/12/2013 12:12 PM, Brad Marks wrote: > >> All, >> >> This is just a curiosity question. >> >> Let's say that I have an Access application that obtains data from a SQL >> Server database via ODBC. >> >> There are 1,000,000 rows in one of the SQL Server tables. >> >> There is a query in the Access application that returns 100 rows from >> this table based on the "Where" condition in the query. >> >> I would guess that the "heavy lifting" is being done by the SQL Server >> database box and only 100 rows are shipped back to the Access application >> on the PC. >> >> Is this correct? >> >> Is this always the case, or is it possible that all of the 1,000,000 rows >> are sent back to the Access application depending on the complexity of the >> SQL? >> >> Again, these are just curiosity questions. >> >> Thanks, >> Brad >> >>