Solved

Error :[ ODBC Sql Server Driver] Timeout expired

Posted on 2001-08-01
8
529 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

770 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