Solved

Error :[ ODBC Sql Server Driver] Timeout expired

Posted on 2001-08-01
8
527 Views
Last Modified: 2007-11-27

When i run following sql statement in SQL Query Analyzer, the statement is valid .  But when i run the statement in VB source code, an error :" [ODBC SQL server Driver] Timeout expired"  will appear in certain time.
Is there any setting to increase the Time from ODBC or VB?  Pls advise.

The following is the statement and vb source code that i use :


sSql = "Select min(wots_code) wots_code from schedule c"    + _ " where c.unit_id = '" + mv_unitid + "'" + _
 " and c.wo_code like ('" + lot_id + "')" + _
 " and c.wots_code in (SELECT DISTINCT a.wots_code FROM ver_ctrl a, user_det b  " + _
" WHERE a.unit_id='" + mv_unitid + "'" + _
"  AND  a.user_id=b.login_id " + _
"  AND  b.user_grp='" + gv_UserGrp + "')" + _
" and c.user_id = '" & mv_userid & "' and (c.sch_type = 'x' or c.sch_type = 'r')"

 adoFcsConnection.Open FcsConnectionString
 adoStaticWotno.Open sSql, adoFcsConnection
0
Comment
Question by:pkp070699
8 Comments
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6339480
You can change the timeout in the ADODB connection, but I'm not sure that it is effective. You can also (I believe) change it in SQL Server management somewhere or other. Is this a table with many records. I also noticed in your SQL clause...

" and c.wo_code like ('" + lot_id + "')"

Shouldn't you put a % somewhere in a LIKE statement?...

" and c.wo_code like ('%" + lot_id + "%')"


Heres the MSDN blurb for the connectiontimeout property of ADODB...

ConnectionTimeout Property
Indicates how long to wait while establishing a connection before terminating the attempt and generating an error.

Settings and Return Values
Sets or returns a Long value that indicates, in seconds, how long to wait for the connection to open. Default is 15.

Remarks
Use the ConnectionTimeout property on a Connection object if delays from network traffic or heavy server use make it necessary to abandon a connection attempt. If the time from the ConnectionTimeout property setting elapses prior to the opening of the connection, an error occurs and ADO cancels the attempt. If you set the property to zero, ADO will wait indefinitely until the connection is opened. Make sure the provider to which you are writing code supports the ConnectionTimeout functionality.

The ConnectionTimeout property is read/write when the connection is closed and read-only when it is open.



0
 

Author Comment

by:pkp070699
ID: 6339569
Hi nigelrowe ,

first, thanks for your advise.

-ya, this table hs many records.
-i hv already convert lot_id ( lot_id = "%" + lot + " %").

the main problem is although i want to change timeout property, but i don't know their statement and source code.  So could u pls show me some simple example vb source code to change the timeout property??

p/s: i'm using ADO, but MSDN help only show others timeout example.

Thanks

Regards
pkp

0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6339919
Hello pkp,

There is also the CommandTimeout property in MSDN....

CommandTimeout Property
Indicates how long to wait while executing a command before terminating the attempt and generating an error.

Settings and Return Values
Sets or returns a Long value that indicates, in seconds, how long to wait for a command to execute. Default is 30.

Remarks
Use the CommandTimeout property on a Connection object or Command object to allow the cancellation of an Execute method call, due to delays from network traffic or heavy server use. If the interval set in the CommandTimeout property elapses before the command completes execution, an error occurs and ADO cancels the command. If you set the property to zero, ADO will wait indefinitely until the execution is complete. Make sure the provider and data source to which you are writing code support the CommandTimeout functionality.

The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same Connection; that is, the Command object's CommandTimeout property does not inherit the value of the Connection object's CommandTimeout value.

On a Connection object, the CommandTimeout property remains read/write after the Connection is opened.
0
 

Author Comment

by:pkp070699
ID: 6342933
hi nigelrowe,

actually i hv already find all reference which relate to "timeout" in MSDN, include the refrence above, but i can't find the actual source code to set querytimeout as MSDN didn't show any example for ADO.  

Regards
pkp
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Accepted Solution

by:
nigelrowe earned 20 total points
ID: 6343762
As proposed by MSDN, the default is 30 seconds. I assume that you change this by...

Conn.CommandTimeout = 60
0
 
LVL 6

Expert Comment

by:Nitin Sontakke
ID: 6344547
You said that you tried Query Analyser without any problems. Can you please tell me the following:

01. Do you log-in thru Query Analyser exactly the same user as the connection object is using to log-in?

02. Have you printed the value of sSQL and then pasted it in Query Analyser and run it without problem? For exactly the same values which gave you the time-out error in VB.

My perception is, your problem is not related with time-out.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6345355
The difference between running the query in Query Analyzer and ADO is that the Query Analyzer defaults to an infinite timeout.  ADO's Connection CommandTimeout property defaults to 30 seconds.

You may want to set the CommandTimeout to 0 to see if this resolves the problem.

Another approach would be to try with the native SQL Server provider.
0
 

Author Comment

by:pkp070699
ID: 6386656
Thanks nigelrowe
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now