[Microsoft][ODBC SQL Server Driver]Timeout expired

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[Microsoft][ODBC SQL Server Driver]Timeout expired

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-12 : 09:05:15

Muhammad Ali writes "hi!

i get error when i access sql server from asp

[Microsoft][ODBC SQL Server Driver]Timeout expired

how can i resolve this problem

but i have chenged the sql server query timeout set to 0

means unlimited query time out"

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-07-12 : 14:52:20

Could you pls post more details regarding you ASP script? It might be your ASP script, which times out, not the SQL Server.

helena

msrviking
Starting Member

7 Posts

Posted - 2002-07-13 : 07:00:07

Hi,
Sorry to disagree Helena. The OLE DB Provider Error as posted in the msg is Time Out Expired is of Sql Server.

Ali, Kindly check up the queries being used by you.. try optimising the query like use joins instead of sub-query, index your tables appropriately, use stored procedures for retrieving data from back-end (Sql Server).

Viking.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-13 : 08:22:17

quote:
Sorry to disagree Helena. The OLE DB Provider Error as posted in the msg is Time Out Expired is of Sql Server

...which is being accessed from an ASP script, and is taking so long to process the information that the connection to SQL Server exceeds its timeout limit.

In all likelihood this is due to a recordset being processed in a loop, similar to this:

While not rsObj.EOF
 response.write rs("someColumn")
 response.write someOtherStuff
 rsObj.MoveNext  'hey, what if this line were missing?  hmmmm?
Wend

I will bet a case of your favorite beer that that's what's causing the timeout problems. Which can be corrected by changing the timeout setting for the ASP SCRIPT (hey, that sounds familiar! Does that sound familiar to you Helena? :)

Server.ScriptTimeout=60 'or increase this number if 60 seconds isn't long enough

The best way to alleviate this problem is to not use a loop through a recordset. Instead, use the GetRows method to dump the data into an array and then process the array. This will minimize the time the ASP page is connected to SQL Server. You can also use GetString. There are a number of links with code snippets:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14388
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10176
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11833
http://www.learnasp.com/learn/whygetrows.asp

These sites have more info on using GetRows and GetString:

www.4guysfromrolla.com
www.learnasp.com
www.asp101.com
www.15seconds.com

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-13 : 10:59:29

>> Server.ScriptTimeout=60

Rob, if its a script timeout, you'd see a script timeout error, not ODBC timeout error! Or am I missing something.

Ali, I listed some troubleshooting steps at: http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm

--
HTH,
Vyas
http://vyaskn.tripod.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-13 : 11:34:09

Yes, that's true. Hmmmm. I think I should have said to increase the CommandTimeout property on the connection object:

connObj.CommandTimeout=60

I keep getting the two mixed up.

But usually if you change one of those settings you also have to change the other; that's been my experience anyway. Because if the connection is open for 60 seconds before timing out, the script timeout will occur, and vice versa.

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-13 : 14:23:14

Yeah, too many types of timeouts

Connection Timeout
Command Timeout
Script Timeout
Query Timeout
Login Timeout

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources