sql server odbc: I have a ATL DLL running in COM+ Applications that communicates with SQL
Server via ADO Command Object. We occasionally get Timeout Expired errors
with one method that is used to call stored procedures that return output
parameters. The error can come back quickly or take a while.

I have done a ton or research. Have looked into Connection Pooling issues,
ODBC settings, ADO, etc.

I need a consultant who is an expert in COM+, ADO and SQL Server to help me
debug this. Please contact me privately if you can help. I'm willing to
pay an hourly rate if you can help me solve this issue.

Thanks

Timeout issues can be caused by a number of problems. Typically they are
caused by a deadlock condition where one process is holding a resource while
attempting another operation. If the process blocking the operation is doing
the same thing you're locked out and pooched. The system can detect this in
some cases.

I think the OUTPUT parameter issue may be a red herring--not related to the
problem.

Consider that the query plan is created the FIRST time a SP is executed and
reused indefinitely until it's pushed out of cache by more important data.
The query plan is created based on the input parameters. If these vary from
call-to-call the "optimal" plan might vary quite a bit. The trick here is to
simplify the SP so instead of calling one 400 line SP, you're calling four
100 line SPs (that call each other as required).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

[quoted text, click to view]

[quoted text, click to view]

Interesting. Thanks for the info.