locked
Update Statement not working RRS feed

  • Question

  • I've got the following update statement:

    UPDATE ISSUE_ACTIONS
    SET BAE_FLAG = 2
    WHERE IA_ISSUE_NO = 399
    AND IA_SEQUENCE = 20

    The fields BAE_FLAG, IA_ISSUE_NO, and IA_SEQUENCE are all of the type int.

    When I run this code inside of my windows app (C#),

    cmd3.CommandText = "UPDATE ISSUE_ACTIONS " +
                                      "SET BAE_FLAG = 2 " +
                                      "
    WHERE IA_ISSUE_NO = 437 " +
                                      "AND IA_SEQUENCE = 13"
    ;

    try
    {
       cmd3.ExecuteNonQuery();
    }
    catch (Exception e)
    {
      
    throw (e);
    }

    I get a timeout error:

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    The statement has been terminated.

    But when I run the SAME statement from Query Analyzer, it executes without a problem.

    Has anyone run into this issue before?  How do I get around this?

    The CommandTimeout property of cmd3 is set to the default because this is not a complex query and should not take more than .5 seconds to execute.

    Tuesday, April 11, 2006 2:46 PM

Answers

  • Figured it out.

    The Profiler worked well, but it didn't show any errors or locks that were being blocked.  I ran the app and then looked in the Processes inside SQL Server to see if any were being blocked and sure enough, 1 was blocking another.  The reason for this is I am executing a Select statement before this update without the (nolock) keyword, so that statement (put in a SQLDataReader) was still open and had a lock on the table.  To fix the problem, I used the (nolock) on my SQL statement and it works fine:

    cmd1.CommandText =
    "SELECT ia.IA_ACTION_CODE, ia.IA_SEQUENCE, " +
    "ia.IA_DETAILS, IA_OVERVIEW, i.* " +
    "FROM ISSUES i (nolock) " +
    "INNER JOIN ISSUE_ACTIONS ia(nolock) ON IM_ISSUE_NO = IA_ISSUE_NO " +
    "WHERE BAE_FLAG = 0";

    sdr=cmd1.ExecuteReader();

    Now the update statement executes fine. 

    Tuesday, April 11, 2006 7:36 PM

All replies

  • have you tried running a trace with SQL Profiler to see what the command object is doing?
    Tuesday, April 11, 2006 3:27 PM
  • No, not yet.  I'll give that a whirl and get back with the results.

     

    Tuesday, April 11, 2006 4:11 PM
  • The SQL Profiler shows that the SQL is coming over correctly, and the event class is SQL:BatchComplete.  There is no other message.

    Tuesday, April 11, 2006 4:24 PM
  • Figured it out.

    The Profiler worked well, but it didn't show any errors or locks that were being blocked.  I ran the app and then looked in the Processes inside SQL Server to see if any were being blocked and sure enough, 1 was blocking another.  The reason for this is I am executing a Select statement before this update without the (nolock) keyword, so that statement (put in a SQLDataReader) was still open and had a lock on the table.  To fix the problem, I used the (nolock) on my SQL statement and it works fine:

    cmd1.CommandText =
    "SELECT ia.IA_ACTION_CODE, ia.IA_SEQUENCE, " +
    "ia.IA_DETAILS, IA_OVERVIEW, i.* " +
    "FROM ISSUES i (nolock) " +
    "INNER JOIN ISSUE_ACTIONS ia(nolock) ON IM_ISSUE_NO = IA_ISSUE_NO " +
    "WHERE BAE_FLAG = 0";

    sdr=cmd1.ExecuteReader();

    Now the update statement executes fine. 

    Tuesday, April 11, 2006 7:36 PM