SQL Server Performance Forum – Threads Archive

Timeout Expired Errors

Hi I run a piece of code that utilises ADO (MDAC 2.6) that opens a recordset. On my test development machine it runs perfectly, but on a slightly slower machine I get the message Timeout Expired. I have set the query timeout value in SQL server itself to 0 (which according to BOL is infinite). I have set in my code that timeouts should also never happen (using ADODB.Connection commandtimeout and connectiontimeout properties), but yet I still get this error. what I find strange is that it kicks this error out almost immediately, even though the first query should theoretically take at least a few seconds to return values. I am using SQL 2000, the database i must admit is horribly designed, and we have been tasked to optimize it. This is driving me insane, please help. Later

Run the PROFILER and capture the trace to know where it lacks, after that submit to index tuning wizrard for recommendations. This will help to fine tune the existing or new indexes to get rid of timeout errors. Also in the articles section have few good references for timeout errors, refer to them. HTH Satya SKJ

Is it a Connection or Command timeout? Do you use ASP or VB? Had to troubleshoot a similar issue wih ASP code. It was a command timeout in our case. Also here the command timeout came very quickly, always within the first second. In this case the web site first of all didn’t use the commandtimeout property as you seem to be using. Then the connection string was located in an ASP session variable. This is never a good place to put it. If you have the connectionstring in a session variable then change it to an application variable or put it in an include file or UDL file. We then upgraded MDAC to 2.7. This was done at the same time so I can’t really say what solved it. Most likely the connection being in a session variable though. One more thing, the server was restarted as well, that’s always a good step to try if everything else fails [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] /Argyle

Thanks for your response… We have solved the problem by setting both the commandtimeout and connectiontimeout of the ADO connection to 0. whether or not this is a good idea is debateable, but it works. I did check the profiler results and ran it through the index tuning wizard with no recommendations.

]]>