Thank you BigLouie. I tried using these for a few of my complex web reports that I created for Sage 100. It did speed up the first one from AP_InvoiceHistoryDetail but it failed on my query that using a running total that works great using linked ODBC tables with the error:

ODBC --call failed

[ProvideX][ODBC Driver]Expected lexical element not found: FROM (#1015)

I googled the error and it looks like with Pass Through Queries the query is run more directly in ProvideX and I cannot use SQL commands that ProvideX does not support.

And by the way - our UPS / FedEx integrations work great and are not slow despite not using Pass Through Queries.

Note that when using pass through queries you should have one query for each table and then have a second query based on that query where you do the running total stuff. That is why you received the error message.

BigLouie,

I was doing it the way you mention but had a typo in one of my queries that was actually causing the error. It's working now.

Old linked ODBC tables: 14 seconds to load report

New Pass through queries: 7 seconds to load report

The downside is that it's a lot more work to create and modify reports as I have to add each table / field that I need manually to a pass through query.

For most reports I use a cached table. For comparison:

Caches Tabled: 0.3 seconds to load report

KDB

Thank you.  I have been using your perfect pass-through for years and years - clean documentation is an asset.

BigLouie,

For some reason, I can't get this document to download. Any thoughts?

Lou

Send me a check with a lot of zeros? Shoot me an email and I'll send you a copy.

Hey Big Louie-Is it OK if the zero's are leading zeros and to the left of the decimal?