SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Timeout expired
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dips255
Starting Member

15 Posts

Posted - 01/06/2009 :  06:41:12  Show Profile  Reply with Quote
Hi

i often get this error on my classic asp pages

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired

is there is any way i can trace the error.

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 01/06/2009 :  07:18:12  Show Profile  Reply with Quote
Your application is not able to establish connection to sql server within the defined connection timeout. Try pinging the server. It could do something with network, maximum number of connections reached etc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/06/2009 :  08:43:08  Show Profile  Reply with Quote
whats the query you were trying to execute. try running this in query analyser and see how long it takes.Seems like query is taking too long to get executed and server timeout period is getting exceeded.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/06/2009 :  10:39:03  Show Profile  Reply with Quote
As visakh says it's most likely a poorly performing query. Post it here and we'll help you solve the problem. If it was a connectivity issue it would most likely throw another error massage...

- Lumbago
Go to Top of Page

dips255
Starting Member

15 Posts

Posted - 01/07/2009 :  00:27:32  Show Profile  Reply with Quote
actually i get this error on some pages
but yesterday i got it from a page where i have not used joins or any other complicated query

its just retrieving UserName,Password from Table and sending it to the user who has to either enter email id or mobile no.

Email = StrValidInput(trim(Request("email")))
fmobile = StrValidInput(trim(Request("fmobile")))
fmcncode = StrValidInput(trim(Request("fmcncode")))
MobileNumber = fmcncode&fmobile

sql="select username,password from usertable where convert(varchar,mobcountrycode,5)+mobile='"&MobileNumber&"' "

or

sql = "select username,password from usertable where email = '"&strEmail&"'"

this error was generated on mobile request

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2009 :  10:40:05  Show Profile  Reply with Quote
and what happened when you ran above queries in query analyser? you got results quickly?
Go to Top of Page

ksogukcesme
Starting Member

Turkey
2 Posts

Posted - 01/10/2009 :  17:31:59  Show Profile  Reply with Quote
you can use "with(nolock)" expression and i recommend you to set an index on the "email" field of usertable. If it has already an index, you should rebuid the indexes with "DBCC DBREINDEX (usertable, '', 70)"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/11/2009 :  01:46:38  Show Profile  Reply with Quote
quote:
Originally posted by ksogukcesme

you can use "with(nolock)" expression and i recommend you to set an index on the "email" field of usertable. If it has already an index, you should rebuid the indexes with "DBCC DBREINDEX (usertable, '', 70)"


use of nolock can lead to dirty reads if data in table is volatile. It depends on whether you're trying to query against tranmsactional or analytical system.
Go to Top of Page

ksogukcesme
Starting Member

Turkey
2 Posts

Posted - 01/12/2009 :  02:45:41  Show Profile  Reply with Quote
"nolock" expression can cause dirty reads but fo this query it is so low possibility.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 01/12/2009 :  07:51:31  Show Profile  Reply with Quote
1) Use parameters rather than building SQL strings. Better performance (less parsing) and much more secure against SQL injection I don't care if you think your StrValidInput function does the job. It's a shitty approach and probably doesn't work properly)

2) Don't use NOLOCK. Unless accurate data is optional, don't use this. It's the preserve of the desparate and people who think 'close enough is good enough', which may be the case but it rarely is necessary.

3) Avoid functions on your predicates because it stops indexes being used. You should store your mobile number in such a way you can convert the search value not the table value. This is the reason your query is slow.
Go to Top of Page

dips255
Starting Member

15 Posts

Posted - 01/19/2009 :  12:05:32  Show Profile  Reply with Quote
Hi

Thanks for all your replies
and regret for responding late

The problem of timeout expired was happening because there was an error on some other asp page where i had kept 'On error resume next' code which bypasses any error and executes next statement. i resolved the error on this page and now its working fine and other pages are also working properly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000