30 second timeout

dexweaver
dexweaver used Ask the Experts™
on
I've created an ADO connection to an SQLDB:

    cnnCreditCards.CursorLocation = adUseClient
    cnnCreditCards.ConnectionTimeout = 240
    cnnBuyPass.Open strConnectString

The connection is established, and all is well.  However, about 10% of the time (this process is run daily), opening a recordset will give me an error in exactly 30 seconds.  The error is:

    Run-time error '-2147217871 (80040e31)':
    [Microsoft][ODBC SQL Server Driver]Timeout expired

The recordset is being opened like this:

    rstDailyListing.Open "spStoredProc", cnnBuyPass, _
     adOpenForwardOnly, adLockReadOnly

Is there another timeout that I need to set for the stored procedure?

dexweaver
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Let me clear this up...is it still Monday???


------------------------------
I've created an ADO connection to an SQLDB:

   cnnCreditCards.CursorLocation = adUseClient
   cnnCreditCards.ConnectionTimeout = 240
   cnnCreditCards.Open strConnectString

The connection is established, and all is well.  However, about 10% of the time (this process is run daily), opening a recordset will give me an error in exactly 30 seconds.  The error is:

   Run-time error '-2147217871 (80040e31)':
   [Microsoft][ODBC SQL Server Driver]Timeout expired

The recordset is being opened like this:

   rstDailyListing.Open "spStoredProc", cnnCreditCards, _
    adOpenForwardOnly, adLockReadOnly

Is there another timeout that I need to set for the stored procedure?

----------------------------------

Thanks for your patience,
dexweaver
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Did you try to use cursor location on server side?
And yes, in SQL Server you have timeouts too!!
By the way, what you stored procedure do too take so long?

Good luck,

Vitor Montalvao

Author

Commented:
I can't use a server side cursor, because I need record counts for another recordset call.

I am aware that SQL Server has timeouts.  The question is why I'm having a 30 second timeout on a connection set for 240 seconds.

The sp takes some time because it is returning a sorted list of every credit card transaction for one day covering 67 convenience stores.  All indexes are optimized and the code usually runs this call in a few seconds.  But sometimes, it pukes.

I guess you've not seen this particular problem before, but thanks anyway.

dex
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Well, you can bypass the record count problem.
Just make a MoveLast operation after open the recordset, and return to first record (MoveFirst). That way you can use recordcount property.

And as you saw, the timeout that really counts is the SQL Server and not the ADO!

For last, I suggest that if you're running your application on a multiusers enviroment, check if when the timeout gives, if no one have tables/records locked, that the sp needs.

Good luck,

Vitor Montalvao
Don't know if this will help or not.

When you work with the DataSourceControl (DSC) of an Office Web Component, you may specify a CommandTimeOut value for the Connection object. When you then build a query or RecordsetDef based on the Connection object, your CommandTimeOut value is ignored. The operation times out at the default value of 30 seconds.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft Office 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

276367 OFF2000: How to Obtain the Latest Office 2000 Service Pack

Author

Commented:
Thanks hes.

I'm working on Windows XP with Office XP, but developing in VB 6.0.  Although the 30 second thing seems similar to my problem, I don't believe that I can apply your suggestion.

dex

Author

Commented:
To Vitor:

When you say:
"And as you saw, the timeout that really counts is the SQL Server and not the ADO!"
I do not know what you mean.

The timeout that really counts?  Is there a seperate timeout on the server side?  Could you explain in detail what you are referring to, please?

Also, you might have to complete that last sentence:
"For last, I suggest that if you're running your application on a multiusers enviroment, check if when the timeout gives, if no one have tables/records locked, that the sp needs."

Thanks,
dex

Commented:
When i looked at your code i saw you did not pass the connection object to the command where you changed the Timeout

Change that to the connection Object where you changed the timeout or change the timeout of the other connection object also ...

I also really sugest that you use a server side SP instead of a client side..

Just create an output paramater that will return the number of rows affected from the SQL Server...

Esp. if you process a lot of recods... It will speed up the process a LOT if you dont transfer them to the client to toss them away...


Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Dex,

I don't know why, but when you set the timeout on ADO object, it's seems that value is ignored (maybe is because you use clientside cursors)!

About my second afirmation, I want to mean that if a user have a table or a record locked (that's happening when someone is deleting, inserting or updating data), then your sp must stop is executing until the table/record has unlock.
Check for this situation!

Good luck,

Vitor Montalvao

Author

Commented:
rdrunner,

I do not understand what you mean.  I have only one connection object.  Please see the second entry, which is a comment to correct my first entry.  I copied and pasted incorrectly on my first attempt to post this question.

to all:

I have changed my code to a server side cursor, and I will test it for a week.  We'll see if any further situations arise!

Thanks
dex

Commented:
Here is a copy& paste from your question :)=

---
cnnCreditCards.CursorLocation = adUseClient
   cnnCreditCards.ConnectionTimeout = 240
   cnnBuyPass.Open strConnectString

The connection is established, and all is well.  However, about 10% of the time (this process is run daily), opening a recordset will give me an error in exactly 30 seconds.  The error is:

   Run-time error '-2147217871 (80040e31)':
   [Microsoft][ODBC SQL Server Driver]Timeout expired

The recordset is being opened like this:

   rstDailyListing.Open "spStoredProc", cnnBuyPass, _
    adOpenForwardOnly, adLockReadOnly

---

Ok you set a Timeout here :

cnnCreditCards.CursorLocation = adUseClient
cnnCreditCards.ConnectionTimeout = 240


then you open your recordset here :

rstDailyListing.Open "spStoredProc", cnnBuyPass, _
    adOpenForwardOnly, adLockReadOnly

The RS uses the cnnBuyPass where no Timeout value was set...

cnnBuyPass.Open strConnectString

This connection was only opend with the default settings and you use it ;)

Author

Commented:
rdrunner,

The code you are referencing was a mistake.

The mistake was corrected by my first comment, the second entry on this list.

I copied and pasted incorrectly on my first submission of this question.

Sorry for the confusion,
dex

Commented:
P.s:Really move to a server side Curser .. If you return more then 1000 Rows

DO IT!!!!

You will Gain a lot of performance... Also

Set your Cache property of the RS to a resonable amount...

My bet is that you return at least 10.000 records in that querry since it takes so long... If oyu use a server side cursor you only need to wait small amounts while you get the next chunk...

Author

Commented:
rdrunner,

As I posted right before your first comment, I am doing it.  I will now wait and see, as I said before.

Thanks for your time, though, all the same.

I'm trying to remember why I chose to use a client side cursor in the first place...I would not have done so unless there was a compelling reason to not use the more obviously logical server side cursor.

dex
Top Expert 2012

Commented:
Some points:

1. Unless you have a particularly slow connection you should not have to set the ConnectionTimeout property.

2. As has been previously pointed out by hes set the CommandTimeout property (of the Command object) to a number greater than the default 30 seconds. You will need to use the Command object (this standard fir setting firehose cursors and will also make it easier to read)

3. Also, previously pointed out, either change your CursorLocation to adUseServer or use the @RETURN_VALUE to return the @@ROWCOUNT

Anthony

Author

Commented:
Ok, I'm still having the problem.

Here is what I changed the code to:

------------
cnnCreditCards.CursorLocation = adUseServer
cnnCreditCards.ConnectionTimeout = 240
cnnCreditCards.Open strConnectString

rstDailyListing.Open "spStoredProc", cnnCreditCards, _
   adOpenForwardOnly, adLockReadOnly
------------

I still receive a timeout message if it takes more than 30 seconds for the recordset to produce its rows.

It is not uncommon for this sp to run for over 30 seconds in the Query Analyzer window to return records.  There is definitely nothing wrong with my connection to the server, I'm simply returning a huge amount of information.  

Since I'm not using a command object, I don't know what else to try.  Any more suggestions?

dexweaver
Top Expert 2012

Commented:
Did you try setting the CommandTimeout property?

Try posting your Stored Procedure.

Anthony

Author

Commented:
acperkins

Please read the posting you're replying to carefully.  I'm not using a command object.  My code shows no command object, so I'm unclear as to how I would go about setting the CommandTimeout property???

My sp is nothing but a select statement.  It returns thousands of rows, and it takes a long time.

Do I need to convert this code to utilize a command object?

dexweaver
Top Expert 2012
Commented:
You can set the CommandTimeout property on the Connection object. However I believe this only effects the Connection's Execute method.  So yes, you will have to use a Command object.  But this is not too painful.

Or you can post your sp and perhaps it can be optimized.

Anthony

Author

Commented:
That appears to have done the trick.

It was a bit painful, but nothing that I'll have nightmares about.  I had to add quite a few lines of code to make this damn thing work!

I believe that I initially chose to go without a command object because it seemed so elegant to call stored procedures directly from the connection object.  Now I have a huge sprawl of verbose code to do the same thing as before except puke!

All griping aside, the next post contains the code that did the trick (for the purpose of anyone looking for the answer).

Thanks to all,
dexweaver

Author

Commented:
Here is the revamped code:

----------
cnnCreditCards.CursorLocation = adUseServer
'NO LONGER NEEDED cnnCreditCards.ConnectionTimeout = 240
cnnCreditCards.Open strConnectString

cmmCreditCards.ActiveConnection = cnnCreditCards
cmmCreditCards.CommandTimeout = 240 '4 minutes
cmmCreditCards.CommandType = adCmdText
cmmCreditCards.CommandText = "spStoredProc"

set rstDailyListing = cmmCreditCards.Execute

'NO LONGER NEEDED rstDailyListing.Open "spStoredProc", _
 cnnCreditCards, adOpenForwardOnly, adLockReadOnly
----------

"Pragmatism is the convenient conclusion reached by
those who lack the patience or intelligence to
formulate a consistant ideology."

Mark G. Hanley

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial