HELP !! Running SQL 2000 using ADO command object from Access 2000

[pointguards]

I'm running a SQL 2000 stored procedure from Access client using
SQLOLEDB provider and ADO Command object. The stored procedure only
returns a Return Value and no recordset.

When ran from Query Analyzer, it runs in less than 4 seconds. When ran
from Access client using objects noted above, it takes whopping 40
seconds and it times out!! Ran SQL profiler, but I cannot see anything
suspicious because it all occurrs within a stored procedure. Event
viewer of the server where the Access client is running from revealed
next to nothing. Is there a chance that overhead from ADO command
object is slowing the execution ?

If anyone could think of a reason why a same procedure will run much
slower within ADO command object, please help!!!!

 

[Sylvain Lafontaine]

A time-out might indicate a permission problem, so you should check that by
1) increasing the time limit to something much higher than 30 seconds ( for
example by setting it to 0 second, which means infinity); 2) giving full
permission to the account used from Access or 3) running the query directly
from Access or running it from QA but this time, using the same account as
the one used in Access.

Besides that, add the WITH RECOMPILE option to the SP to make sure that a
bad query isn't selected (and btw, take the occasion to re-index
everything). A bad query plan execution is the most common source of
problem when a query execute slower when running from Access than from QA.

A third possibility would be a locking problem; for example if you have
opened a table directly from Enterprise Manager while doing your tests from
Access.

Finally, you can raise an error from inside the SP to provide some insight
on its execution.

 

[pointguards]

Thank you Sylvain!! I'll try with recompile option.

Locking probably isnt the cause, because I've checked for any possible
lockings from Process-Info section from Enterprise Manager and I havent
found any process waiting on locks. Or is there a better way to observe
for locking problems??? I guess there are plenty of things I would need
to learn but I havent been able to find a good learning material on
Transactions ( Locks, Isolation, etc) Please show me the light!!

I will also try raising some errors within the SP. You are absolutely
right. Why havent I thought of that.

Thank you very much, Sylvain!! I will let you know what happens!!