| Author |
 Topic  |
|
|
dips255
Starting Member
15 Posts |
 Posted - 01/06/2009 : 06:41:12
|
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
|
| 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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/06/2009 : 08:43:08
|
| 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. |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 01/06/2009 : 10:39:03
|
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 |
|
|
|
dips255
Starting Member
15 Posts |
Posted - 01/07/2009 : 00:27:32
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/07/2009 : 10:40:05
|
| and what happened when you ran above queries in query analyser? you got results quickly? |
|
|
|
ksogukcesme
Starting Member
Turkey
2 Posts |
Posted - 01/10/2009 : 17:31:59
|
| 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)" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/11/2009 : 01:46:38
|
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. |
|
|
|
ksogukcesme
Starting Member
Turkey
2 Posts |
Posted - 01/12/2009 : 02:45:41
|
| "nolock" expression can cause dirty reads but fo this query it is so low possibility. |
|
|
|
LoztInSpace
Aged Yak Warrior
876 Posts |
Posted - 01/12/2009 : 07:51:31
|
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. |
|
|
|
dips255
Starting Member
15 Posts |
Posted - 01/19/2009 : 12:05:32
|
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. |
|
|
| |
Topic |
|
Timeout expired
