ADO Command Timeout when Stored Procedure set to UPDATE or DELETE

The Stored Procedure

I have a stored procedure on SQL Server 2000. Depending on the @Action parameter the sproc will INSERT, UPDATE, or DELETE a record. It then checks for an error, commits the transaction, and returns 1. This works perfectly from SQL Query Analyzer.

CREATE PROCEDURE dbo.spWeb_GoodsInRequestProducts
@Action Int, -- 0 = Insert, 11 = Update, 2 = Delete
@GoodsInRequestRef Int,
@ProductRef Int,
@Qty Int = Null
AS
Set NoCount On

Declare @Error Int
Begin Transaction

If @Action = 0 Begin
    INSERT INTO dbo.tblGoodsInRequestProducts (
        GoodsInRequestRef,
        ProductRef,
        Qty
    ) VALUES (
        @GoodsInRequestRef,
        @ProductRef,
        @Qty )

End

If @Action = 1 Begin
    UPDATE  dbo.tblGoodsInRequestProducts
    SET     Qty = @Qty    
    WHERE   GoodsInRequestRef = @GoodsInRequestRef
    AND     ProductRef = @ProductRef

End

If @Action = 2 Begin
    DELETE FROM dbo.tblGoodsInRequestProducts
    WHERE   GoodsInRequestRef = @GoodsInRequestRef
    AND     ProductRef = @ProductRef

End

Set @Error = @@Error
If @Error = 0 Begin
    COMMIT Transaction
    SELECT 1
End
If @Error <> 0 Begin
    ROLLBACK Transaction
    SELECT 0
End

Set NoCount Off
GO

The ADO Command

Then on an asp classic vbscript web page I have an ado command that passes some parameters to and executes the sproc. This works fine if the @Action is set to 0 (INSERT record), but set to 1 (UPDATE) or 2 (DELETE) and the page times out.

Function GoodsInProduct()
    Dim objCmd
    Set objCmd = Server.CreateObject("ADODB.Command")
    objCmd.ActiveConnection = CONN
    objCmd.CommandType = adCmdStoredProc
    objCmd.CommandText = "dbo.spWeb_GoodsInRequestProducts"     
    objCmd.Parameters.Append objCmd.CreateParameter("@Action", adInteger, adParamInput, , 2)
    objCmd.Parameters.Append objCmd.CreateParameter("@GoodsInRequestRef", adInteger, adParamInput, , 1)
    objCmd.Parameters.Append objCmd.CreateParameter("@ProductRef", adInteger, adParamInput, , 10110)
    objCmd.Parameters.Append objCmd.CreateParameter("@Qty", adInteger, adParamInput, , 8)

    Dim objRS
    Set objRS = objCmd.Execute      
    GoodsInProduct = objRS(0)

    Set objRS = Nothing
    Set objCmd = Nothing
End Function

Response.Write GoodsInProduct

I also have a near identical sproc, called from a near identical ado command on the same page, that will insert, update, and delete without any fuss. Running SQL Profiler, I see the INSERT commands come through, but I never see the UPDATE or DELETE, the page just times out. As I have said, it all works perfectly in the SQL Query Analyzer.

Is anyone able to see what I am doing wrong? Even just an alternative or some way of improving the error checking so I can see where the error occurres.

Thank you.

Things Tried

I have just tried the bare minimum and still times out on the web page but works in SQLQA:

Dim strSQL
strSQL = "DELETE FROM dbo.tblGoodsInRequestProducts WHERE GoodsInRequestRef = 1 AND ProductRef = 10110"

Dim objConn
Set objConn = server.CreateObject("ADODB.Connection")
objConn.Open CONN
objConn.Execute (strSql)
objConn.Close
Set objConn = Nothing

Solved

SOLVED

For anyone in a similar situation: Remember to close SQL Enterprise Manager, SQL Query Analyzer, and SQL Profiler if you run into any problems.

I still don't fully understand the complex inner workings of SQL Server, but I had somehow caused a backlog of TRANSACTIONS that had not been committed. After closing the programs above, the transactions all completed and things worked as expected.

If anyone understands what I had done wrong then please let us all know so that others can stay clear of this happening.