I have an Access Data Project that links via odbc to my SQL Server,
with no problems (I'm running windows NT).  Now, i want to give
someone else access to the SQL Server, so i created the usernames and
gave her a copy of the ADP file and set up the odbc on her computer. 
I can browse my tables from her computer, but when i call a stored
procedure, I get TIMEOUT EXPIRED every time.  Any ideas?

---

Clarification of Question by den2002-ga on 01 Nov 2002 07:39 PST

Query Analyzer is not installed on the users machine, but access runs fine
on another machine.  I can connect enough to browse the tables, and
the stored procedure starts ok, by asking for its variables, but then
it just sits there and times out with an error of TIMEOUT EXPIRED.  In
Access, the connect timeout is set to 0 and the general timeout is set
to 0.  If changing the odbc timeout works then i'll be momre than
happy.

---

Request for Question Clarification by mathtalk-ga on 01 Nov 2002 08:03 PST

Is the problem with one particular stored procedure?  Or with any and
all stored procedures.  Below I outline a test case to do with a
simple stored procedure that will answer this, but you perhaps already
know.

One suspicion I would have in the case of a particular stored
procedure not working (in ODBC but working okay in OLE DB) is that you
are returning more than one record set from the stored procedure, or
perhaps returning data in "messages" (in addition to a record set).

Let's assume that you can use another machine to verify that your user
can connect to SQL Server and run the stored procedure (or procedures)
that way.  You might also mention whether you are using Windows
authentication or mixed mode as the security setting on SQL Server. 
The problem could be as simple as the user having read only access to
the tables but no execute privileges on the stored procedures
themselves.  You can verify this by having your user log in on another
machine and do the queries there (or do it for them if you are SA on
SQL Server and using mixed mode authentication with SQL Server login
for the user).

One thing to be aware of in connection with stored procedures that
work under OLE DB connections (as used by Query Analyzer and
Enterprise Manager) but not under ODBC connections (as used by Access)
is that by default an ODBC "data source" will reset certain things
like concatenation of Nulls with strings to the ANSI standard.  This
is one pitfall.  But as I reread your question, the failure is
occurring with all stored procedures, not just with a particular one.

Normally you would have this problem on your machine equally as on the
user's machine, but perhaps you fixed this in the past and had
forgotten about it?

Let's do this.  Create a "trivial" stored procedure, perhaps just a
select with a simple WHERE clause using a parameter, against one of
the smaller tables in the database.  Try to invoke it directly from
the Query menu on your user's machine in Access and see if that works.

If it does, try and invoke one of the previous stored procedures the
same way.   I'm thinking that you may be using Access to hide
implementation details from the user, so if you are able to invoke the
stored procedures directly, we can perhaps see any error messages
better.  If it still fails, but doesn't provide a more useful
explanation than TIMEOUT EXPIRED, we can turn on error logging in ODBC
(there's also a trace option for ODBC but I've never been able to get
useful information from it with queries that fail).

I'd be curious to know how the user will invoke the stored procedures,
if it involves code behind a form or whatever besides the Query menu
approach.  Of course we are still looking for a reason it works on
your machine and not on the user's machine; I'm just wondering if
maybe the process involves another machine or user dependence that is
not obvious.

Experience has made me leery of using Access to make a production SQL
Server database available to users or even to developers with limited
database backgrounds, esp. with attached tables and VBA code on the
Access side.  If you have to do this, using stored procedures to
return recordsets (as you seem to trying) is probably as good an
approach as any.

regards, mathtalk-ga

---

Clarification of Question by den2002-ga on 01 Nov 2002 09:02 PST

What this is, is an Access .ADP file that has client list reports that
ask you for a date of birth range and other parameters and then prints
a report to the printer.  The stored procedure is the datasource
behind the access report so that when she click a button on a form, it
will ask her for the parameters and then print the report.  The
problem is not with the report as i went into the database on her
machine and tried to run the stored procedure behind the report
directly and it still times out.
I created the trivial stored procedure and ran it on her machine with
no problems, but the list printing stored produres (theres 3 of them)
still return TIMEOUT EXPIRED errors.  These three stored procedures
are searching a fairly large database (around 2 million records), so
it takes a little time to complete.

She is running Windows 98, authenticating into an NT Network, and i'm
using the NT authentication on the SQL Server.  Permissions are all
good as far as I can see.  I really want to get this working as she
can take some of the more mundain printing tasks off my workload!
Thanks,
Den

---

Clarification of Question by den2002-ga on 01 Nov 2002 11:59 PST

I just tried to build an ASP front for the process, thinking i could
make a web version of the report and stick it out on our intranet and
let her print it from there, but guess what! - the web page is timing
out too ([Microsoft][ODBC SQL Server Driver]Timeout expired ).  This
is starting to look like an odbc timeout issue.  Let me know where i
have to change the odbc timeout values.
Thanks,
Den

Hi, Den:

Since the dbconnection property in Access is set to 0 (right? in your
Access code?), I'm suspecting a driver incompatibility.  The following
should gather information about that, and also handle the case of
truly long running queries.

The ODBC connections can be configured from an applet in the Control
Panel.  In Windows NT it's called "ODBC Data Sources" and has a yellow
32 in the icon (to distinguish it from 16-bit ODBC drivers).  If you
created the data source from within Access, then it will probably show
up under the "File DSN" tab.

More to the point, your user is running Win98.  The Control Panel
applet says "ODBC Data Sources (32bit)" and the icon lacks the flashy
yellow 32.

These applets can create, remove, and configure the ODBC data sources,
as well as provide version information about the underlying ODBC
drivers.

One possibility is a version conflict between the ODBC SQL Server
driver on your user's machine and the version of SQL Server you are
trying to connect her to.  If you check that information (and the
version on your NT machine, as well, for the sake of completeness), I
can look into that.

At any rate if you highlight the data source in question (under the
File DSN tab, or possibly under User DSN depending on how security was
set on the Win98 machine), and click the Configure button, it goes
throw a configuration "wizard".  The second "page" of this wizard has
a "Client Configuration..." button, that basically corresponds to the
SQL Client Configuration Utility.  Of those settings the most
important is the choice of Network Library, probably either TCP/IP or
Named Pipes.  I would make the user's configuration consistent in this
respect with what is working on your NT machine.  Close the client
configuration utility and go back to the ODBC data source
configuration wizard.

There may be a checkbox at the bottom of that second page that offers
to obtain additional settings by connecting to SQL Server (requires
you to enter the loginID, which should be all you need if you are
using Windows authentication).  This can be useful as a quick test
that SQL Server is reachable (you cannot proceed if it cannot connect,
because it pops up an error message), but eventually you will simply
want to be consistent with your own set up.

The next page offers to create temporary stored procedures, which you
don't need in this case, for "prepared SQL statements".  I'd leave
this unchecked but it shouldn't be a big deal either way.  Leave the
Use ANSI quoted identifiers and Use ANSI nulls _checked_, unless you
suspect the stored procedure might depend on non-ANSI behavior in a
specific way.  You probably don't have a failover SQL Server, so leave
the last box unchecked.

Next page! We finally come to some settings that might help solve the
problem.  Leave the Perform translation for character data box
checked; it's probably the only one checked by default, and leave Use
regional settings unchecked.

Add checks to the Save long running queries to the log file (might as
well use the default filename, C:\QUERY.LOG, and to the Log ODBC
driver statistics to the log file (by default, C:\STATS.LOG).  The
default query time for sending stuff to file is 30000 milliseconds, or
30 seconds.

You can leave that for now.  Click the finish button.  Bring up Access
and do some things to exercise the ODBC connection (like running the
trivial stored procedure from the Pass Through Query definition), or
just browsing the list of linkable tables (under Import Data...) on
SQL Server.  See if that puts much of anything into the Log file; I've
never found much interesting there, but it may help in the next step. 
Try and run the report, and see if it either works or at least puts
the error message in some sort of context with the logging file.

regards, mathtalk-ga

---

Clarification of Answer by mathtalk-ga on 02 Nov 2002 09:56 PST

The first thing I said may be a little confusing. dbconnection refers
to an ODBC connection string attribute.  Within your Access code you
would refer to it as:

qdf.ODBCTimeout

an attribute of a QueryDef object taking integer values, i.e. 0 means
no timeout.

-- mathtalk-ga

---

Request for Answer Clarification by den2002-ga on 04 Nov 2002 06:20 PST

just a quick question.  The database doesn't use any code.  It's just
a report that has a stored procedure as its datasource.  When the user
prints the report, the stored procedure ask for it's parameters. 
Therefore, I can't set dbconnection to 0 as you started in your
answer.  In the datalink properties in the connection setting in
access, under the advanced tab, i have 'connection timeout' value set
to 0, and in the 'all' tab I have general timeout set to zero as well.

Anyway, i went back and checked my ODBC settings as you suggested. 
The SQL odbc driver version on my computer (the one that works) is
2000.80.194.00.  The version on the users machine is 2000.80.525.00,
which i assume is a later version?  I dont know if i can 'downgrade'
the driver. If you know, let me know!

I checked the log file options and ran the simple stored procedure
again, which worked.  I then ran the long stored procedure, which did
the 'timeout expired' thing again.  I checked to see if it had created
a log file, but it didnt.

Is there a way that i can set the ODBC timeout in code, even if i am
just running a report linked to the stored procedure?

Thanks,
Den

---

Request for Answer Clarification by den2002-ga on 04 Nov 2002 08:03 PST

Ok.  I decided to rethink the whole procedure.  I made the stored
procedure append data to a table and linked the report to that table. 
I then created a modure that runs the stored procedure and set the
timeout to 0 in the Vb code.  It's kind of stupid that I had to do
this to get it to work, but we are talking microsoft here!
Thanks for all the help.
Den.

---

Clarification of Answer by mathtalk-ga on 04 Nov 2002 09:40 PST

Hi, Den:

Thanks for taking time to rate my answer; it means a lot.

Yes, it is kind of frustrating to have to rework the process without a
clear "Microsoft philosophy" guiding the design.  If you had time, of
course, you could try all the permutations to see what is really
essential in this case.  We still don't know why it worked on your
machine and not on the Win98 machine, though I suspect it is an
operating system/device driver dependence of some sort.

Congratulations on battling it through!

regards, mathtalk-ga

The researcher was more than helpful in his answer.  He steered me on
the right path to solving my problem.  Very well done.

If you have Query Analyzer installed on the user's machine, try
calling the stored procedure from there.

It certainly sounds like the default timeout limit on the ODBC
connection you created (for Access to use on the user's machine) needs
to be increased.

If you verify that Query Analyzer can connect and run the query
successfully, then I'd be happy to give an answer about how to
increase the ODBC timeout.

regards, mathtalk-ga

Query Analyzer is not installed on the users machine, but it runs fine
on another machine.  I can connect enough to browse the tables, and
the stored procedure starts ok, by asking for its variables, but then
it just sits there and times out with an error of TIMEOUT EXPIRED.  In
Access, the connect timeout is set to 0 and the general timeout is set
to 0.  If changing the odbc timeout works then i'll be momre than
happy.