Timeout Expired

Question

Post reply

Timeout Expired

isay

SSC Enthusiast

Points: 192
More actions
March 14, 2006 at 2:20 am

#68772

I am running a stored procedure in my program. When going through the recordset, the connection always timeouts. When I run my store procedure in a query analyzer, my stored procedure completed more than 1 minute. I already filtered out my query to all the data that I needed.
I set
cnn.ConnectionTimeout = 0
but still got the same error. I am encountering that error in the loop of my code while inserting the data in the the sql table from my stored procedure. My data is about 15,000 records.
Kindly advise. Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32271 More actions · Answer 1

Can we see that piece of code?

Is there a linked server involved?

David Burrows SSC Guru Points: 65141 More actions · Answer 2

ConnectionTimeout is for timeout on connecting to SQL Server what you want is CommandTimeout, which is in seconds and has a default of 30.

Set CommandTimeout to just above the longest query time. I would not set this to zero (infinite).

Far away is close at hand in the images of elsewhere.
Anon.

isay SSC Enthusiast Points: 192 More actions · Answer 3

' heres the piece of my code. please need you expertise because Im just a starter and a little lost..

Set cnn = New ADODB.Connection

Set rstHybrid = New ADODB.Recordset

cnn.ConnectionTimeout = 1200

cnn.Open ("DSN=Hybrid")

cnn.BeginTrans

cnn.CommandTimeout = 45

if........then...

cnnNSI.Execute ("Delete from ExpHybridVN where impexp_ctry='VN'")

rstHybrid2.Open mProcName8, cnn, adOpenDynamic, adLockReadOnly, adCmdStoredProc

' IN THIS DO WHILE WHERE I ALWAYS ENCOUNTER TIMEOUT EXPIRED

Do While Not rstHybrid2.EOF

cnnNSI.Execute ("insert ExpHybridVN(impexp_ctry,impexp_date,ship_id,ship_ref,impexp_port)" & _

" values ('" & rstHybrid2!impexp_ctry & "','" & rstHybrid2!impexp_date & "','" & rstHybrid2!ship_id & "','" & rstHybrid2!ship_ref & "','" & _

rstHybrid2!impexp_port & "')")

rstHybrid2.MoveNext

mCTR = mCTR + 1 ' I SET COUNTER TO CHECK HOW MANY RECORDS

Loop

rstHybrid2.Close

THanks in advance.

Jo Pattyn SSC-Dedicated Points: 32271 More actions · Answer 4

*is it the insert that takes longer than a minute or the whole batch?

*First thing to do is retrieving all requested records, since not-yet transferred records hold locks.

'todo, convert to stored procedure

cnnNSI.Execute ("Delete from ExpHybridVN where impexp_ctry='VN'")

'is it necessary to see changes?

'otherwise

'rstHybrid2.Open mProcName8, cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

'performs better

rstHybrid2.Open mProcName8, cnn, adOpenDynamic, adLockReadOnly, adCmdStoredProc

if rstHybrid2.recordcount>0 then 'any records?

rstHybrid2.movelast 'fetch all records

rstHybrid2.movefirst

Do While Not rstHybrid2.EOF

'todo, convert to stored procedure

cnnNSI.Execute ("insert ExpHybridVN(impexp_ctry,impexp_date,ship_id,ship_ref,impexp_port)" & _

" values ('" & rstHybrid2!impexp_ctry & "','" & rstHybrid2!impexp_date & "','" & rstHybrid2!ship_id & "','" & rstHybrid2!ship_ref & "','" & _

rstHybrid2!impexp_port & "')")

rstHybrid2.MoveNext

'doevents in case you want some response

mCTR = mCTR + 1 ' I SET COUNTER TO CHECK HOW MANY RECORDS

Loop

end if

rstHybrid2.Close

isay SSC Enthusiast Points: 192 More actions · Answer 5

Hi,

It worked... I am so thankful. I'm glad you guys are there.

isay SSC Enthusiast Points: 192 More actions · Answer 6

Hi... just a follow up question

For other stored procedure, the code you suggested is already working.

Now, I have another store procedure which is for about 8mins to complete when run in query analyzer. I used the same procedure but the record count is zero(0).

Is it because my stored procedure took so long and it is not successfully return the data? Do you have any suggestion?

Thanks again in advance.

Jo Pattyn SSC-Dedicated Points: 32271 More actions · Answer 7

Hello again.

Normally when there is a time-out you get an error.

The stored procedures does return the results in the query analyzer?

isay SSC Enthusiast Points: 192 More actions · Answer 8

yes... it return a record but it took me for about 8 mins to 10 mins to complte the query.

So I assume it is one one the cause thats why when my program pass thru the stored procedure then made a record count, it return 0.

Need you help. Thanks.

Jo Pattyn SSC-Dedicated Points: 32271 More actions · Answer 9

Your commandtimeout is adjusted to 10 minutes?

Is there any rollbacks of transactions?

You can post your query in the T-SQL forum if you need to speed it up.

isay SSC Enthusiast Points: 192 More actions · Answer 10

Hi, I just changed my stored procedure and it is now doing okay so far... thanks

With the above code you suggested, In line below, it doesn't return any record

'rstHybrid2.Open mProcName8, cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

So I change it to

rstHybrid2.Open mProcName8, cnn, adOpenStatic, adLockReadOnly, adCmdStoredProc

Then its working okay. But now, with that line, I am encountering again 'Timeout Expired'. Is this connection issue with the server or with my code? Kindly advise because I am always stuck with this and I am new with this thing.

Thanks you so much.

isay SSC Enthusiast Points: 192 More actions · Answer 11

I am really sorry. Got it now. Some error in my code. Thanks again