Hi,
I'm running a SQL server 2000 (recently upgraded to SP4, MDAC 2.81) on
Win2k box with 1GB memory. I've two huge denormalized tables: one Master
table with 70 columns and 5million rows and another Summary table with fewer
columns and 2 million rows.

Problem:
When I try to run a simple <select * from table> kind of query from Access
or Query Designer within Enterprise Manager, it works and I get data back.
The minute I add a simple where condition or run a query with 3-4 columns
with a group by, it fails with a:
ODBC Call Failed [ODBC SQL Server Driver] Timeout Expired(#0) error in Access
and in Query Designer (on the server box!) something similar happens:
[ODBC SQL Server Driver] Timeout Expired
Same query against the summary table may work most of the time using both
clients.

So, I've spent the last few days searching the forums, KB's etc and realize
that this is a client issue and specifically a query timeout issue since I do
NOT have problems running any kind of query using Query Analyzer or surprise:
Microsoft Query (Excel) against either table. Not being able to use Query
Designer doesn't matter much, but if running queries via Access doesn't
work..then I'm in serious trouble. Being able to use Access to access the SQL
server data is key, because of the large datasets and the ease with which
financial analysts can customize their queries for their specific needs.

While I work on normalizing my database, things I've tried so far with no
success:
1. Added indexes to the tables, shrunk database, checked available space,
updated statistics etc..
2. Set Query Governor on server to unlimited, remote server connection
timeout to unlimited too
3. Logged long running queries in Client configuration of SQL server DSN and
set query time to max of 99999 milliseconds
4. Set OLE/DD timeout within Access's Tools-->Options to max of 300
5. Run a trace in SQL Profiler and see if I could get any clue ...
6. Restarted SQL server a few times to get rid of ghost sessions, locks etc..
7. Used Access and Query Designer on the same box as the SQL server to
eliminate network issues
8. Changed my ODBC DSN to use SQL pipes instead of TCP/IP

This is driving me absolutely crazy. Especially the fact that Excel using
the same client ODBC SQL server DSN used within Access, can run any query
without timing out, but not Access!
I've used Access to run queries against same 5million row table in MySQL
(using MySQL ODBC driver) and it works fine..Only downside is that I've to
install MySQL ODBC driver on all desktops and it is unsupported

Can someone please help me make Access work with SQL server 2000 or is this
just an ODBC bug that affects big tables?

Thanks for listening
John H.

Bottom line: don't DO that. As you have seen, it won't work and will
never work the way you are going about it.

Access was originally designed (a dozen years ago) to be a desktop
database connected to the low-end Jet engine, not a general purpose
querying tool for fetching server data consisting of millions of rows.
Its basic architecture is essentially unchanged since then. If you
want to use it as a FE to SQLS, then you need to design a
query-by-form interface that builds queries with WHERE clauses that
restrict the number of rows fetched from the server. There are a ton
of Access books that have been written over the years that tell you
exactly how to construct such a UI.

Part of developing a successful and robust application is picking the
right tool for the job, or understanding the limitations of the tool
you are stuck with, and working around those limitations. You can use
Access as long as you honor its limitations and obey the golden rule
of fetching less data.

--Mary

On Fri, 13 May 2005 19:26:04 -0700, "BI_Specialist"
<BISpecialist@discussions.microsoft.com> wrote:

>Hi,
>I'm running a SQL server 2000 (recently upgraded to SP4, MDAC 2.81) on
>Win2k box with 1GB memory. I've two huge denormalized tables: one Master
>table with 70 columns and 5million rows and another Summary table with fewer
>columns and 2 million rows.
>
>Problem:
>When I try to run a simple <select * from table> kind of query from Access
>or Query Designer within Enterprise Manager, it works and I get data back.
>The minute I add a simple where condition or run a query with 3-4 columns
>with a group by, it fails with a:
>ODBC Call Failed [ODBC SQL Server Driver] Timeout Expired(#0) error in Access
>and in Query Designer (on the server box!) something similar happens:
>[ODBC SQL Server Driver] Timeout Expired
>Same query against the summary table may work most of the time using both
>clients.
>
>So, I've spent the last few days searching the forums, KB's etc and realize
>that this is a client issue and specifically a query timeout issue since I do
>NOT have problems running any kind of query using Query Analyzer or surprise:
>Microsoft Query (Excel) against either table. Not being able to use Query
>Designer doesn't matter much, but if running queries via Access doesn't
>work..then I'm in serious trouble. Being able to use Access to access the SQL
>server data is key, because of the large datasets and the ease with which
>financial analysts can customize their queries for their specific needs.
>
>While I work on normalizing my database, things I've tried so far with no
>success:
>1. Added indexes to the tables, shrunk database, checked available space,
>updated statistics etc..
>2. Set Query Governor on server to unlimited, remote server connection
>timeout to unlimited too
>3. Logged long running queries in Client configuration of SQL server DSN and
>set query time to max of 99999 milliseconds
>4. Set OLE/DD timeout within Access's Tools-->Options to max of 300
>5. Run a trace in SQL Profiler and see if I could get any clue ...
>6. Restarted SQL server a few times to get rid of ghost sessions, locks etc..
>7. Used Access and Query Designer on the same box as the SQL server to
>eliminate network issues
>8. Changed my ODBC DSN to use SQL pipes instead of TCP/IP
>
>This is driving me absolutely crazy. Especially the fact that Excel using
>the same client ODBC SQL server DSN used within Access, can run any query
>without timing out, but not Access!
>I've used Access to run queries against same 5million row table in MySQL
>(using MySQL ODBC driver) and it works fine..Only downside is that I've to
>install MySQL ODBC driver on all desktops and it is unsupported
>
>Can someone please help me make Access work with SQL server 2000 or is this
>just an ODBC bug that affects big tables?
>
>Thanks for listening
>John H.

Thank you for taking the time to respond and appreciate your honesty
regarding Access's limitations. Definitely didn't expect such unequivocal
talk from Microsoft about their own tools. Unfortunately, it appears that you
completely missed my question...either I was too long winded or you were too
busy to actually read my post.

I'm sure its my karma. As part of IT, for years I have chastised "power"
users in Finance or Sales for using Excel and Access against production
Databases as query/reporting tools when there was something like Business
Objects/Cognos etc available. Now, having landed in Finance and not having
access to any IT resources, I've actually come to realize that querying
functionality within Excel or Access isn't so bad, although they do have
their limitations.

Using Access as FE to SQL server is definitely not my first choice. At the
same time, scores of folks use Access to query databases all the time. What I
find ironic is that although I'm no big fan of Access as a FE for querying, I
seem to be in the unlikely situation of having to defend its capabilities in
this regard. As I mentioned before, I used Access to run the same query
against the same 5million records sitting in a MySQL table, with where
conditions etc and didn't get a odbc timeout. If I were to go along with what
you were saying, I'd have to believe that the open source community has
figured out a way around Access's limitations...something that Microsoft's
own engineers didn't or couldn't.

Anyways, peronal commentary aside, let me clarify the symptoms of my problem
again:
1. I AM trying to fetch less data via use of where clauses, when I query
against a SQL server based table (linked via ODBC). And that is exactly when
I get the timeout error. Didnt' matter if it was a Select Query or a
Pass-thru query, still got the ODBC timeout, even though I was pulling in 4-5
fields only and maybe 250 rows after grouping.
2. I can understand if my query times out when trying to retrieve ALL 5
million rows from SQL server..but noooo...that works fine!! Is this is a
"limitation" of Access? Don't understand how a Query-form interface would
help or be any different than a pass-through query, if the SQL being sent to
the server is the same.

Good news is that I searched the Access newsgroup and found out where I
could change the ODBC timeout value from the default 60seconds to 300 (buried
under Query Properties). Problem solved- No more ODBC call failed error
message!!

But if someone could please tell me where the timeout is set within
Enterprise Manager's Query Designer, I'd be very grateful. The symptoms are
identical to Access (can view all records, but can't apply any conditions),
but not sure what the connection type is and how to configure it. I guess
everyone uses Query Analyzer, so this has never been an issue.

Sorry about another long post.

Thanks,
John H.

"Mary Chipman [MSFT]" wrote:

> Bottom line: don't DO that. As you have seen, it won't work and will
> never work the way you are going about it.
>
> Access was originally designed (a dozen years ago) to be a desktop
> database connected to the low-end Jet engine, not a general purpose
> querying tool for fetching server data consisting of millions of rows.
> Its basic architecture is essentially unchanged since then. If you
> want to use it as a FE to SQLS, then you need to design a
> query-by-form interface that builds queries with WHERE clauses that
> restrict the number of rows fetched from the server. There are a ton
> of Access books that have been written over the years that tell you
> exactly how to construct such a UI.
>
> Part of developing a successful and robust application is picking the
> right tool for the job, or understanding the limitations of the tool
> you are stuck with, and working around those limitations. You can use
> Access as long as you honor its limitations and obey the golden rule
> of fetching less data.
>
> --Mary
>

On Mon, 16 May 2005 18:26:03 -0700, "BI_Specialist"
<BISpecialist@discussions.microsoft.com> wrote:

>1. I AM trying to fetch less data via use of where clauses, when I query
>against a SQL server based table (linked via ODBC). And that is exactly when
>I get the timeout error. Didnt' matter if it was a Select Query or a
>Pass-thru query, still got the ODBC timeout, even though I was pulling in 4-5
>fields only and maybe 250 rows after grouping.

The idea is to perform all data processing on the server, not the
client. Sometimes a Profiler trace can help you troubleshoot. Another
option is to code complex queries as stored procedures, which
guarantees that all processing is done on the server. If you are using
Access queries, then you have to be aware that using functions or
expressions in queries can cause processing to bog down since the
expression service must then process each row individually.

Using Access queries against linked ODBC tables can work, but you have
to be careful in coding them.

>2. I can understand if my query times out when trying to retrieve ALL 5
>million rows from SQL server..but noooo...that works fine!! Is this is a
>"limitation" of Access? Don't understand how a Query-form interface would
>help or be any different than a pass-through query, if the SQL being sent to
>the server is the same.

As in my answer to 1), it depends on what is being processed where.
Without seeing actual query syntax, it's hard to tell. Take a look at
a Profiler trace to analyze the SQL. Even if the SQL sent to the
server is the same, it may not be processed the same way.

Let's clarify what is and what isn't a limitation of Access. When you
do a SELECT * FROM in an Access query, you're not really fetching all
million rows. You're only fetching the first few rows and the key
values to the remaining rows, in what is known in Access as a dynaset,
or keyset cursor. As you scroll through the UI, the data in the
remaining columns is fetched. This creates a situation where locks are
held on the data and the network is continuously in use fetching rows.
You don't get all million rows in a gulp, even though you may think
so. The real limitation here is that Access is tying up network and
server resources while holding on to that keyset cursor. This design
pattern works OK when it's an Access-Jet app, but is terrible for an
Access-SQL app.

>But if someone could please tell me where the timeout is set within
>Enterprise Manager's Query Designer, I'd be very grateful. The symptoms are
>identical to Access (can view all records, but can't apply any conditions),
>but not sure what the connection type is and how to configure it. I guess
>everyone uses Query Analyzer, so this has never been an issue.

Tools|Options|Connections|Query Timeout. QA is not meant to be an
end-user tool for querying data, which is why it's not an issue.

Bottom line: appearances can be deceptive. If you want to build a
light-weight querying application in Access going against SQL Server
data that can handle more than a handful of users without bogging down
the network or the server, then you need to understand that Access
DOES NOT work out of the box, nor was it ever intended to. You'll
solve most of your problems if you learn how to code it the right way
using stored procedures, which you can call from pass-through queries.
Just stay away from cursors :-)

--Mary

Thanks again for a detailed response. Lots of good information which I do
appreciate. I realize I'm functioning in a far-from-ideal kind of situation
and its not going to scale as more users use Access/Excel to run ad-hoc
queries against SQL server. Its hopefully a band-aid fix for the next 6
months that allows some work to be done, while more heavy-duty and IT
approved tools/projects come online.

The need for the group of financial analysts I support, is some ad-hoc query
capability via the existing MS Office tool set (can't install any new
software etc..). Therefore stored procedures called via pass-thru queries
are not applicable. They are used to waiting 30+ min for their queries to run
against the company's transactional system (Oracle), so waiting just 2-4 min
for their Access/Excel queries to fetch data from SQL server is still a huge
win. I'm sure once I build a true normalized database or dimensional datamart
out of my big flat file table, query times should be well under the 60 sec
default timeout. Till then, I can instruct them to set the ODBC timeout
within Access to a higher value.

"Mary Chipman [MSFT]" wrote:
> >But if someone could please tell me where the timeout is set within
> >Enterprise Manager's Query Designer, I'd be very grateful. The symptoms are
> >identical to Access (can view all records, but can't apply any conditions),
> >but not sure what the connection type is and how to configure it. I guess
> >everyone uses Query Analyzer, so this has never been an issue.
>
> Tools|Options|Connections|Query Timeout. QA is not meant to be an
> end-user tool for querying data, which is why it's not an issue.

> --Mary

I think you got confused between QA and QD (thats within EM). The qeury
timeout default within QA is 0, so its never an issue. However my question
was with regards to the hidden-Access-like-GUI based query interface within
Enterprise Manager ...called Query Designer in my SQL server Bible book.
This is the one that times out like Access and there seems to no place for
me to set the timeout value higher.

Also, your info about how Access quries place undue load on network/server
resources and your last statement about QA not being an end-user tool (I
agree with you) leads me to ask:
Whats the best client or most efficient way to query data in SQL server for
an end-user, that is user-friendly (no coding required) and does not bog down
the server?
Is Microsoft planning such a tool or 3rd party tools my only safe bet?

Thanks again,
John H.

On Tue, 17 May 2005 13:04:04 -0700, "BI_Specialist"
<BISpecialist@discussions.microsoft.com> wrote:

>Whats the best client or most efficient way to query data in SQL server for
>an end-user, that is user-friendly (no coding required) and does not bog down
>the server?
>Is Microsoft planning such a tool or 3rd party tools my only safe bet?

The best way is to code as much as possible in stored procedures,
expecially if the result set is going to be read-only, and let the
users execute the stored procedures. If you want to provide the
illusion that users are querying against base tables, you can create
views that reflect a subset of the data and let the users select from
the views. Unfortunately there isn't anything out of the box that can
create efficient queries because so much of what constitutes
efficiency is the relational design of the tables
(overnormalized=bad), how aggregates are created (on server=good, on
client=bad), indexing, network speed, etc. -- all things that have
nothing to do with the tools you select for the client to use on the
FE. So the bottom line is that the developer has to do a lot of the
coding on the back end to make it (a) a seamless, code-free experience
for the user on the front end, and (b) an efficient application.

--Mary

*** Sent via Developersdex http://www.developersdex.com ***