MS SQL Server ODBC - Problem using Access or Query Designer to run queries in SQL Serve

Author

Problem using Access or Query Designer to run queries in SQL Serve

BI_Specialist

2005-05-14, 3:23 am

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.

Mary Chipman [MSFT]

2005-05-14, 9:23 am

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@discuss
ions.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.

eric debie

2005-05-26, 3:23 am

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

Sponsored Links