Solved

Error :[ ODBC Sql Server Driver] Timeout expired

Posted on 2001-08-01
8
531 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 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