timeout problem with asp and sql server 7

timeout problem with asp and sql server 7

Post by Grégory Broncha » Thu, 15 Aug 2002 02:39:35



Hello,

I experience a strange problem.

I execute a sp in query analyzer and it takes less than 1 sec.
When I want to call this sp in an asp page with ado, I receive this
error message:

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
/[SOME PAGE.asp], line 77

You will find hereafter codes from my asp page and sp

Thanks in advance for you help

Greg

**
Here is my sp call in asp:

Function Add2Basket(id_basket,id_product,quantity)
  Set objCmdAdd2Basket = Server.CreateObject("ADODB.Command")
  With objCmdAdd2Basket
    .ActiveConnection = objConn
    .CommandText = "spAdd2Basket"
    .CommandType = adCmdStoredProc
    .Parameters.Append
.CreateParameter("RETURN_VALUE",adInteger,adParamReturnValue,,0)
    .Parameters.Append

    .Parameters.Append

    .Parameters.Append

    .Execute,,adExecuteNoRecords                       '<<<< HERE IS
LINE 77 >>>
    Add2Basket = .Parameters("RETURN_VALUE").value
  End With
End Function

**
Here follows the code of my sp:


AS

SET NOCOUNT ON

BEGIN
BEGIN TRANSACTION



/* Si qt demande = 0 ==> effacement de la ligne */

  BEGIN  
    DELETE BASKET_ITEM


      BEGIN

      END
  END  

ELSE
  BEGIN

    /* Rcuprer le dernier prix affich */


    FROM PRODUCT


      BEGIN

      END  

    /* Vrifier que le produit n'est pas dj dans le panier */

    FROM BASKET_ITEM


      BEGIN

      END  

    /* Si changement de quantit */

      BEGIN
        UPDATE BASKET_ITEM



          BEGIN

          END
      END  

    /* Si nouveau produit */
    ELSE
      BEGIN
        INSERT INTO BASKET_ITEM
        (id_product,id_product_type,id_basket,price,quantity)


          BEGIN

          END
      END
  END  

  /* commit ou rollback */

    BEGIN
      ROLLBACK TRAN
      RETURN (0)
    END

  ELSE
    BEGIN
      COMMIT TRAN
      RETURN (1)
    END
END

 
 
 

timeout problem with asp and sql server 7

Post by Erland Sommarsko » Thu, 15 Aug 2002 07:02:28



> I experience a strange problem.

> I execute a sp in query analyzer and it takes less than 1 sec.
> When I want to call this sp in an asp page with ado, I receive this
> error message:

> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
> /[SOME PAGE.asp], line 77

> You will find hereafter codes from my asp page and sp

One reason for this could be that you are blocking yourself. While the
procedure is running, perform an sp_who, and check the Blk column if
there is any blocking. A DBCC INPUTBUFFER on the blocking spid and the
blocked spid gives an idea of what the processes are up to.


>       BEGIN

>       END



the IF statement is always successful.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

timeout problem with asp and sql server 7

Post by Grégory Bronchar » Thu, 15 Aug 2002 07:43:12


Hi,

Thanks for your help.
As you have seen I am not so used to write sp..
Could you please give more details about what I need to change in my code?
And why is everything running perfectly in query analyzer?

TIA
Grgory

> One reason for this could be that you are blocking yourself. While the
> procedure is running, perform an sp_who, and check the Blk column if
> there is any blocking. A DBCC INPUTBUFFER on the blocking spid and the
> blocked spid gives an idea of what the processes are up to.


> >       BEGIN

> >       END



> the IF statement is always successful.

 
 
 

timeout problem with asp and sql server 7

Post by Erland Sommarsko » Fri, 16 Aug 2002 06:42:40



> As you have seen I am not so used to write sp..
> Could you please give more details about what I need to change in my code?

Could not see anything more apparent. But since I don't know the
data model you are working with, I don't know what more hidden traps
there might be.

Quote:> And why is everything running perfectly in query analyzer?

Please see my previous posting for one suggestion.

There are other possibilities as well, but presuming that all columns
you use in search conditions are indexed, and that there are no triggers
involved, it is not likely that there is a bad query plan involved. Thus,
my guess that it is a blocking issue. Did you try sp_who as adviced you?

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp