Timeout expired

Timeout expired

Post by Dave Shoa » Thu, 15 Mar 2001 03:46:17



I am trying to copy four access tables to SQL server tables and I receive
the error:

Run-time error '-2147217871 (80040e31)':
[Microsoft][ODBC SQL Server Driver]Timeout expired

I am using the following code for each table:

Dim adoCon As ADODB.Connection, adoSQLCon As ADODB.Connection
Dim rsSQL As ADODB.Recordset, rsDP As ADODB.Recordset
Dim strCon As String, strSQL As String

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Medstat\translate.mdb;Persist Security Info=False"
Set adoCon = New ADODB.Connection
adoCon.CursorLocation = adUseClient
adoCon.Open strCon

strCon = "Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties='DSN=Hewey;Description=Hewey
;WSID=ISSUPSPEC;DATABASE=DP;Trusted_Connection=Yes'"
Set adoSQLCon = New ADODB.Connection
adoSQLCon.CursorLocation = adUseClient
adoSQLCon.Open strCon

Set rsSQL = New ADODB.Recordset
Set rsDP = New ADODB.Recordset

strSQL = "select * from dental98"
rsSQL.Open strSQL, adoSQLCon, adOpenDynamic, adLockOptimistic

strSQL = "select * from dental_1998"
rsDP.Open strSQL, adoCon, adOpenDynamic, adLockOptimistic

While Not rsDP.EOF
rsSQL.AddNew
rsSQL!field1= rsDP!field1
rsSQL!field2= rsDP!field2
rsSQL.Update
rsDP.MoveNext
Wend

rsSQL.Close
rsDP.Close

adoCon.Close
adoSQLCon.Close

The error occurs on the rsSQL.Update line. How can I find out what is
causing this error or increase the timeout? The tables are quite large, two
have 1 million records each, and two have about 200,000 records. (I get the
error in all four tables.)

Thanks,
Dave

 
 
 

Timeout expired

Post by SQL Serve » Wed, 28 Mar 2001 07:38:53


Check out Knowledge Base article Q208386 regarding changing the QueryTimeout
value for ODBC in the registry.


Quote:> I am trying to copy four access tables to SQL server tables and I receive
> the error:

> Run-time error '-2147217871 (80040e31)':
> [Microsoft][ODBC SQL Server Driver]Timeout expired

> I am using the following code for each table:

> Dim adoCon As ADODB.Connection, adoSQLCon As ADODB.Connection
> Dim rsSQL As ADODB.Recordset, rsDP As ADODB.Recordset
> Dim strCon As String, strSQL As String

> strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\Medstat\translate.mdb;Persist Security Info=False"
> Set adoCon = New ADODB.Connection
> adoCon.CursorLocation = adUseClient
> adoCon.Open strCon

> strCon = "Provider=MSDASQL.1;Persist Security Info=False;Extended
> Properties='DSN=Hewey;Description=Hewey
> ;WSID=ISSUPSPEC;DATABASE=DP;Trusted_Connection=Yes'"
> Set adoSQLCon = New ADODB.Connection
> adoSQLCon.CursorLocation = adUseClient
> adoSQLCon.Open strCon

> Set rsSQL = New ADODB.Recordset
> Set rsDP = New ADODB.Recordset

> strSQL = "select * from dental98"
> rsSQL.Open strSQL, adoSQLCon, adOpenDynamic, adLockOptimistic

> strSQL = "select * from dental_1998"
> rsDP.Open strSQL, adoCon, adOpenDynamic, adLockOptimistic

> While Not rsDP.EOF
> rsSQL.AddNew
> rsSQL!field1= rsDP!field1
> rsSQL!field2= rsDP!field2
> rsSQL.Update
> rsDP.MoveNext
> Wend

> rsSQL.Close
> rsDP.Close

> adoCon.Close
> adoSQLCon.Close

> The error occurs on the rsSQL.Update line. How can I find out what is
> causing this error or increase the timeout? The tables are quite large,
two
> have 1 million records each, and two have about 200,000 records. (I get
the
> error in all four tables.)

> Thanks,
> Dave


 
 
 

1. [OBDC SQL Server Driver][Timeout Expired] - How to reconfigure the timeout

When running a query on a very large database, I receive this error after
30-50 seconds:
[Microsoft][OBDC SQL Server Driver][Timeout Expired]

We are using NT and 98. I receive this error message running the queries on
the client and the server. We just switched over from Access 97, which was
unable to handle the amount of data. Using Access 97 ODBC pass-through
queries, we recevied a similar error message, but it was a simple fix -
select properties and change the timeout.

This doesn't happen with queries that return only a small number of rows.
The queries I am receiving the error message on are attempting to return 7-8
million rows.

The default timeout setting is 10 minutes, and so far that is the only
setting I can find.

I appreciate any help on this matter!

2. Certified Oracle 7 DBA & Sys (12 yrs Exp) - US Citizen

3. timeout expired and/or PCAnywhere hangs???

4. For one login,i want one computer to log

5. TIMEOUT EXPIRED

6. Key Field Auto Increase.

7. ODBC SQL Server Driver: Timeout Expired (80040e31)

8. How to eliminate the dependency of capital/small letters?

9. Timeout Expired

10. timeout expired!!!

11. Timeout Expired Error w/ SQL 7

12. ?Timeout expired

13. MS SQL 7.0 : Timeout expired!!!