Update statement not working

[robertfah]

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.

 

[NoCoolHandle]

My advise would be to run the SQL trace tool (profiler)

Start->Programs->Microsoft SQL Server->Profiler..


Then open a new "Trace" with all the defaults..(you might filter to just your db or app, but that will just limit some of your returns if the server is busy..

Then open your app and run the code.

In trace look to see what your application is sending.. My guess is something is going on in the background that you are missng.. CN string that sort of thing..

HTH


Rob

 

[robertfah]

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.