| Author |
Topic |
|
dips255
Starting Member
17 Posts |
 Posted - 2009-01-06 : 06:41:12
|
| Hii often get this error on my classic asp pagesMicrosoft OLE DB Provider for SQL Server error '80040e31' Timeout expired is there is any way i can trace the error. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-06 : 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
52326 Posts |
Posted - 2009-01-06 : 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
3271 Posts |
Posted - 2009-01-06 : 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
17 Posts |
Posted - 2009-01-07 : 00:27:32
|
| actually i get this error on some pagesbut yesterday i got it from a page where i have not used joins or any other complicated queryits 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&fmobilesql="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
52326 Posts |
Posted - 2009-01-07 : 10:40:05
|
| and what happened when you ran above queries in query analyser? you got results quickly? |
|
|
|
ksogukcesme
Starting Member
2 Posts |
Posted - 2009-01-10 : 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
52326 Posts |
Posted - 2009-01-11 : 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
2 Posts |
Posted - 2009-01-12 : 02:45:41
|
| "nolock" expression can cause dirty reads but fo this query it is so low possibility. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-01-12 : 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
17 Posts |
Posted - 2009-01-19 : 12:05:32
|
| HiThanks for all your repliesand regret for responding lateThe 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. |
|
|
|
|
Timeout expired