Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error :[ ODBC Sql Server Driver] Timeout expired

Posted on 2001-08-01
8
Medium Priority
?
541 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 
LVL 3

Accepted Solution

by:
nigelrowe earned 60 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 11

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Suggested Courses

927 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