Re: Microsoft OLE DB Provider for SQL Server error '80040e31'

Raj (Raj@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I'm getting the Timeout error in one of my ASP application. I'm calling a
slight complex stored procedure which is running fine when run
in the Query Analyzer.

Does fine mean that it executes in less than 30 seconds, or does
1 minute count as "fine"?

But when it is called from the ASP code in certain cases when the number
of records are more it is giving the following error message:

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired

By default most client APIs has a default timeout of 30 seconds. (A
completely idiotic idea if you ask me.)

Following is the code I'm using to call the stored proc.:

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "Sel_MOD_Changes"
Set rs_Item = cmd.Execute(recsAffected, Array(POSEQNUM), adCmdStoredProc)

Add conn.CommandTimeout = 0 to set an infinite time out. Do this before
you create the command object.

You can also set the CommandTimeout on the command object if you like.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Is 40,000 rows too much for xml output from ado.net?
    ... using the ado.net command object. ... into memory tables (dataTables). ... writing that data to Sql Server. ... >As you have discovered we are missing proper bulk update ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored Procedure call and passing parameters
    ... I would like to use the ado command object to pass parameters to a stored ... procedure in a MS2000 SQL server. ... I need to do some modification and was wondering if Excel would support and ...
    (microsoft.public.excel.programming)
  • RE: RE LINK DATASOURCE AT RUN TIME
    ... You can use ADO objects to run the stored procedure. ... Dim cnn As ADODB.Connection ... ' Get the proc's return code from the Command object. ... > Because the query is very complex, it need be done a my SQL server stored ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL for SP
    ... You can invoke an Sp as the source for a recordset. ... > Instead of using this SP for Command object with CommandType ... > Set parTemp = New ADODB.Parameter ... > much more experience with VB than with SQL Server and have more control on ...
    (microsoft.public.vb.database.ado)
  • Re: Named parameters w/o stored procedure - possible or not?
    ... standpoint, the best is preparing the command once, then execute it with ... SP's for SQL Server come in two flavours: ... They are created when a Command object is setup correctly and cease to exist ... set Prepared to true for SQL Server, it will take care of that itself. ...
    (microsoft.public.vb.database.ado)
Loading