DB Connection timeout

[Hondy]

Hi

I'm running a stored procedure via ASP, I have the following variables set in the script but the page still times out at 30 seconds? I appreciate 30 seconds is a long wait but this is acceptable to me, I want to make it 60 seconds or longer, why does it cut out at 30 seconds?

connectiontimeout = 80000
commandtimeout = 80000
Server.ScriptTimeout = 80000

IIS is set at 900 seconds timeout.

The same stored procedure works fine when executed by Excel so I'm guessing it is an ASP related issue?

Thanks

 

[vicvirk]

where is it timing out - you should have a line number in your error...and what does the rest of the page look like (especially where the time out occurs)

TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[DreXor]

how long does the stored proc take on the sql server?



DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[Hondy]

vicvirk - it says line 19, assuming that "includes" are counted then it is this:

Function strGetConnectString()
strGetConnectString = MM_dataconn_STRING
End Function

Drexor - the query takes 30 seconds to run, every time the page either times out or displays properly at 30 seconds.
I realise this is the default command time-out but I have supposedly overridden this...

Any help?

Thanks

 

[DreXor]

connectiontimeout and commandtimeout are methods/properties of your ado object, so those need to be

DbConnection.commandtimeout = 8000
DbConnection.connectiontimeout = 8000

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[Hondy]

Hi Robert

Yes i was being kind of lazy, the timeouts have an associated object I just never wrote it in my post.

Any ideas why they aren't working?

set conn = CreateObject("ADODB.Connection")
conn.connectiontimeout=8000
conn.commandtimeout=8000
Server.ScriptTimeout = 8000

Thanks

 

[DreXor]

been a long time since i've used extensive "chew" scripts for data crunching, i've looking in my archives and for some reason i'd put timeouts on all three properties as zero

although i did frequently use response.buffer=true and response.flush a period(space) after every so often records processed as a visual indicator to the client side it's still working. which may have assisted in not timing out, but not entirely, because i did have troubles back in the day with them timing out as well

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[Hondy]

yeh if you put zeros it means there is no time-out, I tried that and no luck there either :s

 

[DreXor]

Hmmm, wil do more thinking (with google in hand!) and respond back soon

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[wvdba]

should that be 80000 or 8000? it's in mili-seconds.

 

[DreXor]

well he's saying 30 seconds for the SP, so 8000 would timeout, i've just taken it to lazy typing as i usually do, but if it is at 8000 instead of 80k then it will be timing out

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[gmmastros]

There are certain performance improvements that may be made to the stored procedure to get it to return the data faster. I mean.... suppose you re-work the procedure so it returns in 3 seconds instead of 30. That would solve your problem. Most queries can be made faster. If you post your query here, I'll take a look at it to see if improvements can be made.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Hondy]

thanks gmmastros its appreciated but I can't post the query for security concerns, and I'm kinda stuck with the query as is anyway as i'm just calling a pre-existing SP that I'm not allowed to change.

As for the 8000, I didn't realise it was milliseconds, I thought it was in seconds. I'll increase that value and see how I go, although I would expect it to time-out after 8 seconds instead of 30.

I appreciate the input guys

Cheers

 

[Hondy]

i'm talking rubbish, it was 80K so it should last 1min 20secs - but i'm still getting timeouts after 30secs??