Error :[ ODBC Sql Server Driver] Timeout expired


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
pkp070699Asked:
Who is Participating?
 
nigelroweCommented:
As proposed by MSDN, the default is 30 seconds. I assume that you change this by...

Conn.CommandTimeout = 60
0
 
nigelroweCommented:
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
 
pkp070699Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
nigelroweCommented:
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
 
pkp070699Author Commented:
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
 
Nitin SontakkeDeveloperCommented:
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
 
Anthony PerkinsCommented:
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
 
pkp070699Author Commented:
Thanks nigelrowe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.