Board index MS SQL Server

Runtime error '2147217871 (80040e31)' Timeout Expired.

Runtime error '2147217871 (80040e31)' Timeout Expired.

Post by Vams » Fri, 05 Dec 2003 16:35:17



Hai

can someone suggest me what is the problem with my code?
1.  Actually i keep getting the error:

Runtime error '2147217871 (80040e31)' Timeout Expired.

I am using  "cnPubs.ConnectionTimeout = 0"
this command in my code so it should not give me a
timeout error.But i keep getting the error.

There are many joins in my stored procedure and the data
is huge,,lakhs of records..Is the error because of the
huge data???? i dont understand.
My procedure is as follows..I am using this procedure

Sub DataExtract()

Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
cnPubs.ConnectionTimeout = 0

Dim strConn As String

strConn = "PROVIDER=SQLOLEDB;"

strConn = strConn & "DATA SOURCE=(PRODUCTXP);INITIAL
CATALOG=PROREPORTING;"
'strConn = strConn & "DATA SOURCE=(local);INITIAL
CATALOG=LTFG;"

strConn = strConn & " INTEGRATED SECURITY=sspi;"

cnPubs.Open strConn
cnPubs.ConnectionTimeout = 0
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnPubs
cmd.CommandText = "PRO_SP"
cmd.CommandType = adCmdStoredProc

cmd.Execute

cnPubs.Close

Set cnPubs = Nothing

End Sub

The stored procedure runs for about 3 minutes.

2.  How do i import this data to excel sheet? Actually
this code is in visual basic editor of the excel sheet.
how do i dump the data to the excel sheet??

Thanks in advance for any help
Vamsi

 
 
 
Top

Runtime error '2147217871 (80040e31)' Timeout Expired.

Post by oj » Fri, 05 Dec 2003 16:46:24


You want to set the CommandTimeOut property to unlimited.

e.g.
cmd.CommandTimeout = 0

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


Quote:> Hai

> can someone suggest me what is the problem with my code?
> 1.  Actually i keep getting the error:

> Runtime error '2147217871 (80040e31)' Timeout Expired.

> I am using  "cnPubs.ConnectionTimeout = 0"
> this command in my code so it should not give me a
> timeout error.But i keep getting the error.

> There are many joins in my stored procedure and the data
> is huge,,lakhs of records..Is the error because of the
> huge data???? i dont understand.
> My procedure is as follows..I am using this procedure

> Sub DataExtract()

> Dim cnPubs As ADODB.Connection
> Set cnPubs = New ADODB.Connection
> cnPubs.ConnectionTimeout = 0

> Dim strConn As String

> strConn = "PROVIDER=SQLOLEDB;"

> strConn = strConn & "DATA SOURCE=(PRODUCTXP);INITIAL
> CATALOG=PROREPORTING;"
> 'strConn = strConn & "DATA SOURCE=(local);INITIAL
> CATALOG=LTFG;"

> strConn = strConn & " INTEGRATED SECURITY=sspi;"

> cnPubs.Open strConn
> cnPubs.ConnectionTimeout = 0
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
> Set cmd.ActiveConnection = cnPubs
> cmd.CommandText = "PRO_SP"
> cmd.CommandType = adCmdStoredProc

> cmd.Execute

> cnPubs.Close

> Set cnPubs = Nothing

> End Sub

> The stored procedure runs for about 3 minutes.

> 2.  How do i import this data to excel sheet? Actually
> this code is in visual basic editor of the excel sheet.
> how do i dump the data to the excel sheet??

> Thanks in advance for any help
> Vamsi


 
 
 
Top

Runtime error '2147217871 (80040e31)' Timeout Expired.

Post by Uri Diman » Fri, 05 Dec 2003 17:10:48


Vamsi
Consider using DTS  for this purpose.


Quote:> Hai

> can someone suggest me what is the problem with my code?
> 1.  Actually i keep getting the error:

> Runtime error '2147217871 (80040e31)' Timeout Expired.

> I am using  "cnPubs.ConnectionTimeout = 0"
> this command in my code so it should not give me a
> timeout error.But i keep getting the error.

> There are many joins in my stored procedure and the data
> is huge,,lakhs of records..Is the error because of the
> huge data???? i dont understand.
> My procedure is as follows..I am using this procedure

> Sub DataExtract()

> Dim cnPubs As ADODB.Connection
> Set cnPubs = New ADODB.Connection
> cnPubs.ConnectionTimeout = 0

> Dim strConn As String

> strConn = "PROVIDER=SQLOLEDB;"

> strConn = strConn & "DATA SOURCE=(PRODUCTXP);INITIAL
> CATALOG=PROREPORTING;"
> 'strConn = strConn & "DATA SOURCE=(local);INITIAL
> CATALOG=LTFG;"

> strConn = strConn & " INTEGRATED SECURITY=sspi;"

> cnPubs.Open strConn
> cnPubs.ConnectionTimeout = 0
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
> Set cmd.ActiveConnection = cnPubs
> cmd.CommandText = "PRO_SP"
> cmd.CommandType = adCmdStoredProc

> cmd.Execute

> cnPubs.Close

> Set cnPubs = Nothing

> End Sub

> The stored procedure runs for about 3 minutes.

> 2.  How do i import this data to excel sheet? Actually
> this code is in visual basic editor of the excel sheet.
> how do i dump the data to the excel sheet??

> Thanks in advance for any help
> Vamsi

 
 
 
Top

1. Run-time error '-2147217871 (80040e31): Timeout Expired

We have our Management Reports, created with an old version of Visual Basic
on XP Professional Workstations. It uses Crystal Reports Version 8.5 to
query our SQL Database (Version 7) which is installed on an NT 4.0 Server
(w/ the latest service packs and hot fixes).

On May 16th we installed the latest hotfixes, 811493, 816093, & Q815021 on
the NT 4.0 server.  Then we shut it down.  We moved all the roles, except
for the Schema role because we forgot it until a week later, to the second
domain controller and then we installed from scratch 2003 server on the
first domain controller.  We renamed our domain (which was one of our
intentions) and for some reason we could not get exchange to work.  So we
reinstalled 2000 server on the first DC and replicated Active Directory
over, leaving the roles on the second DC.

That day, for some strange reason, our Management Reports stopped working.
The give this error:

(((Run-time error '-2147217871 (80040e31)':  Timeout expired)))

That is the same message we got on all four XP Prof. clients that have the
VB program.

Now, we have tested, using Access, the SQL Server and its tables.  We've
tested the stored procedures.  The Access database failed until we changed
the DDE timeout from 30 seconds to 60.

Giving the domain controller information was only because I wanted to be
complete with this description.  Although, I don't believe that has anything
to do with our management reports erroring out.

I guess I need to know, how do you lengthen the timeout period on the VB
program so that it wont timeout out?

Thanks in advance to anyone who can help or to anyone with some advice.

Sincerely,

George W. Jackson
Computer Tech.

2. Mailing List Info Request

3. Run-time error '-2147217871 (80040e31)' Timeout expired

4. resizing of the transaction log

5. ODBC Timeout expired '80040e31'

6. SQL - way to select the top 10 / top 20 from a database

7. Error -2147217871 from Microsoft OLE DB Provider for SQL Server: Timeout expire

8. VB 4.0/Access 97/Trying to avoid an error...

9. Error: -2147217871 Timeout expired

10. Error -2147217871: Timeout expired

11. Error -2147217871:[Microsoft][ODBC SQL Server Driver]Timeout expired

12. Timeout ODBC Drivers error '80040e31'

13. Status no 2147217871 (Microsoft) (ODBC SQL SERVER DRIVER) timeout expired


All times are UTC
Board index