SQL Server Performance Forum – Threads Archive

Time out expired

I use a command object from vb in access 2000 to run a stored procedure which creates a table on the server which I then import into an access database. The first time I run it, after a while I get the message "time out expired" and the table is not created. If I run it immediately again, it will run perfectly. I have the query time out setting in SQL server set to 0 (unlimited). What could be the problem.

There are 2 different kinds of timeouts that can affect you, a connection time out and a query timeout. How they are set differs by the means with which you are executing a query. Based upon that, are you connecting to Enterprise Manager, Query Analyzer, or something else (e.g. a custom application) when the time out occurs? If it is from a custom application, are you using the ODBC API’s directly, or are you using the OLE-DB drivers for ODBC? Is the time out occurring while you are connecting to the server, or while executing a query? Satya SKJ

I have experienced same problem before. I’ll say the problem is related to the Server or/and I/O.

Id say satya is right in implying (i think) its a client side problem. The connection timeout can be specified by adding "Connect Timeout=XXX" to the ADO Connection string (default=15), and this specified the amount of time to wait for a connection to sql server to be established before it gives up. The query timeout value is normally set via a property of the command object. Its my understanding that the server is not notified of this value, and so the timeout is purely client side, rather than telling the server to timeout the query if it hasnt completed in N secs.

]]>