Re: Another SQL Server Question

On Jan 26, 10:06 am, "Rick Brandt" <rickbran...@xxxxxxxxxxx> wrote:

Actually that is completely correct.  What can vary is how much of the work
needing to be done by those queries can be passed to the server to perform and
how much has to be processed locally.

The biggest thing is for the WHERE clause to be executed on the server because
that is what limits the rows brought over.  Joins and/or other things that might
be just fine with an Access back end might make it impossible for the server to
perform the row filtering.  When that happens a lot of unnecessary data is
pulled from the server.

It is rare for a query to not work at all after moving the tables to ODBC links.
It is somewhat common though for a query to not work very efficiently after
doing so.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com


Thanks for the reply Rick. So do you see anything in the calculated
field in my query grid (original post) that would cause SQL Server to
give the "ODBC-call failed" error that I am seeing? As mentioned in
the original post, if I remove the reference to the form field
([Forms]![QDelays]![DateSpan] from the calculated field in the query
grid the problem goes away.

References to form fields in the "where" section of the query grid
work fine as they always have. It's just the reference to the form
field in the query's calculated field "SchedDelay" that is causing the
problem.

If I switch my query to SQL view, this what it looks like:

SELECT Count(MinerDelays.MDID) AS CountOfMDID, LU_Delays.DelayType,
LU_Delays.DelayDesc, Sum(MinerDelays.DelayMins) AS SumOfDelayMins,
[SchedDelay]*[Forms]![QDelays]![DateSpan]*60 AS SchedDelay2
FROM (LU_Panel INNER JOIN (LU_Crew INNER JOIN (LU_Shift INNER JOIN
ShiftProdDetails ON LU_Shift.ShiftID = ShiftProdDetails.Shift) ON
LU_Crew.CrewID = ShiftProdDetails.Crew) ON LU_Panel.PanelID =
ShiftProdDetails.Panel) INNER JOIN (LU_DelayType INNER JOIN (LU_Delays
INNER JOIN MinerDelays ON LU_Delays.DelayCode = MinerDelays.DelayCode)
ON LU_DelayType.DelayType = LU_Delays.DelayType) ON
ShiftProdDetails.ProdShiftID = MinerDelays.ProdShiftID
WHERE (((LU_Shift.Shift) Like [Forms]![QDelays]![Shift]) AND
((ShiftProdDetails.TxtDate) Between [Forms]![QDelays]![FromDate] And
[Forms]![QDelays]![ToDate]) AND ((LU_Crew.Crew) Like [Forms]![QDelays]!
[Crew]) AND ((LU_Panel.Panel) Like [Forms]![QDelays]![Panel]))
GROUP BY LU_Delays.DelayType, LU_Delays.DelayDesc,
[SchedDelay]*[Forms]![QDelays]![DateSpan]*60
HAVING (((LU_Delays.DelayType) Like [Forms]![QDelays]![DelayCat]))
ORDER BY Sum(MinerDelays.DelayMins) DESC;

The reference in the SELECT statement to the "Datespan" form field on
the "QDelays" form is the problem.
.

Relevant Pages

  • Re: Adding new row with default values.
    ... The solution based on acquiring default values from the server seems for me ... effectively performs the insert operation using "INSERT INTO" SQL statement. ... > is query the server for the default values of the table, ... > inner join syscolumns as scol on so.id = scol.id ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Query execution plan different between production/test - same
    ... Tweaked Query and execution plan on Prod server that runs fast: ... INNER JOIN ShopFloor.dbo.vShifts Shifts ON Operations.vchOperation = Shifts.vchOperation AND Shifts.vchOperation'DEVELOPMENT' ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2000 dies on JOIN query
    ... I have quite disturbing situation where I am not happy about the way how SQL handles the query. ... Situation is related to using user function in INNER JOIN select. ... My question is if this self-destructive behavior of SQL server can be prevented by some configuration parameters or patches. ...
    (comp.databases.ms-sqlserver)
  • Re: Adding new row with default values.
    ... is query the server for the default values of the table, ... You can get the information with this query from the database: ... inner join syscolumns as scol on so.id = scol.id ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: MailMerge hangs and crashes with Access on Server
    ... Since I am fairly new to working in this environment, I am not sure what you mean by an "Access group" with help to restructuring the query? ... "Peter Jamieson" wrote: ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)