Results 1 to 10 of 10
  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: SQL Server 2000 Error

    Hi,

    I have a question to the SQL Server experts.
    I have a kind of problem with an system that is trying to modify some records in a SQL Server 2K Database.

    Server configuration:
    Windows Server 2003 Standard Edition
    MDAC 2.81 SP2
    SQL Server 2000 SP3

    The error that I receive is:
    Description [Timeout expired], NativeError [0] SQLState [HYT00], Source [Microsoft OLE DB Provider for SQL Server], Number [-2147217871]

    It happens when I try to execute a store procedure that updates a record and is just with that procedure, there's no error when I execute procedure to insert rows.

    I would feel so much thankful about any suggestions.
    Last edited by druriel; 05-09-07 at 11:04.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The update is taking more than 30 seconds to run. You may want to look over the query plan for that update. Perhaps you need to add an index, or re-evaluate the where clause.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or he is blocking himself.
    Post your update procedure for us to review.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2007
    Posts
    3
    You are right, and here I go.

    The primary key for the table are these fields (Mvt_NumTrn, Mvt_CodTran).

    And the store procedure that i'm trying to execute is the following:

    CREATE PROCEDURE spuModificarEstadoMovto @NumTrn VARCHAR(16), @CodTrn VARCHAR(3), @StdTrn TINYINT, @CodRptHost SMALLINT,
    @Resultado BIT OUTPUT
    WITH ENCRYPTION
    AS
    IF ( @CodRptHost = 9998 )
    UPDATE tbl_Movimiento SET Mvt_StdTrn = @StdTrn
    WHERE ( Mvt_NumTrn = @NumTrn ) AND ( Mvt_CodTran = @CodTrn )
    ELSE
    UPDATE tbl_Movimiento SET Mvt_StdTrn = @StdTrn, Mvt_CodRptHost = @CodRptHost
    WHERE ( Mvt_NumTrn = @NumTrn ) AND ( Mvt_CodTran = @CodTrn )

    IF( @@ROWCOUNT > 0 )
    SET @Resultado = 1
    ELSE
    SET @Resultado = 0

    GO


    Thanks for any suggestion.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How many records in tbl_Movimiento? Is there a single index on the two fields Mvt_NumTrn and Mvt_CodTran?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not sure this will solve your problem, but this more streamlined code may make it easier to debug:
    Code:
    CREATE PROCEDURE spuModificarEstadoMovto @NumTrn VARCHAR(16), @CodTrn VARCHAR(3), @StdTrn TINYINT, @CodRptHost SMALLINT,
    @Resultado BIT OUTPUT
    WITH ENCRYPTION
    AS 
    
    UPDATE	tbl_Movimiento
    SET	Mvt_StdTrn = @StdTrn,
    	Mvt_CodRptHost = coalesce(nullif(@CodRptHose, 9998), Mvt_CodRptHost)
    WHERE	Mvt_NumTrn = @NumTrn
    	AND Mvt_CodTran = @CodTrn
    
    SET @Resultado = convert(bit, @@ROWCOUNT)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    May 2007
    Posts
    3
    Actually there are 454.000 rows in tbl_movimiento, there is just one index, the clustered primary key (Mvt_NumTrn, Mvt_CodTran).

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That should nail the index head on. Check if there is blocking going on, as Blindman suggested.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Also, execute your procedure from Query Analyzer and run a showplan to make sure it is using the index.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2007
    Posts
    71
    pls check if there are any processes updating the same table. This might be a blocking issue

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •