Re: Problem using Access or Query Designer to run queries in SQL S

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.
.

Relevant Pages

  • Re: How does one set read-uncommitted on the entire DB?
    ... queries to determine if the locking will become less of a problem. ... All client APIs permit you to set the timeout, ... Note that query timeout is unrelated to SQL Server. ...
    (comp.databases.ms-sqlserver)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.server)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.programming)
  • Re: Ongoing purging of active records causes deadlocks
    ... For a query like: ... plan for some of our queries and it looks like it sorts first, ... What is a possible alternative, though, is that the purge first performs: ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)