Post reply

Update table hangs

  • January 2, 2004 at 2:22 pm

    #83741

    We are facing a peculiar problem while updating a table. The update happens thru

    a stored procedure. When the procedure is

    run thru ISQL, it works fine. The table can

    also be updated by firing direct update

    from VB front-end appl. thru ODBC call.

    However the update hangs when called thru

    this procedure. After doing a re-build by

    dropping and re-creating the table, it worked

    couple of times, but hangs again now.

    Can anyone provide any clues? or more information required?

    The table has approx. row size of 250

    and number of rows about 530.

    Thanks in advance.

  • January 2, 2004 at 2:30 pm

    #488529

    It would be helpful if you could provide more information like table structure, the update statement.

    Without knowing more an answer will be like a shot in the dark.

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 2, 2004 at 2:55 pm

    #488531

    pravins,

    I agree with Frank about the guessing. Also, when you mean "hangs" how long does the process run before (I assume) you kill it?

    Have you attempted to see if the proc is blocking/being blocked??

    I would also like to see the code that is causing the problem ...

    Good Luck

    AJ Ahrens

    webmaster@kritter.net



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • January 2, 2004 at 7:21 pm

    #488539

    We definately need more info as Frank Kalis and whoteegan point out. However, here's a shot in the dark. If the SP works well once or twice and then stops working well, yet the update always works well outside of the SP, perhaps you need to re-evaluate a plan each time you attempt the update.

    Have you tried putting a with recompile clause in the SP?

  • January 5, 2004 at 1:16 am

    #488629

    What is the timeout setting for ODBC connection?

  • January 5, 2004 at 1:27 am

    #488630

    Run that proc again. Let it hang again. monitor the server locks during the execution period. specially sp_who and sp_lock

    If possible post results of your findings.


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

  • January 6, 2004 at 1:50 am

    #488761

    I would also recommend putting some sort of logging code into your front end app.

    When I have my code in a DLL then every function has an error trapping clause that appends details of the error to an DLLError.Log file in the application path.

    This does sound like a locking problem though.

    LinkedIn Profile

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply