Link to home
Start Free TrialLog in
Avatar of Cosine_Consultants
Cosine_Consultants

asked on

SQL UPDATE does NOT work

Dear all,
I have a serious problem with my SQL server 2000 that I could not pinpoint and fix.

I write an UPDATE SQL command for a specific row in a table and execute it through the SQL Query Analyser tool. Although I do NOT get any error messages, the row is NOT updated. If I go and change the row explicitly through SQL Server Enterprise, everything is OK. I tried this on two different databases; on one it works whereas not on the other. Here's an example:

SELECT owner_code
FROM owner
WHERE owner_id=123; --I see the owner_code (equals to 5)

UPDATE owner
SET owner_code=8
WHERE owner_id=123; --I get a message that my "Command executed successfully"

SELECT owner_name
FROM owner
WHERE owner_id=123; --the owner_code does NOT change !!!!!!!!!


I have never seen such an error with ANY database. Anyone have any ideas? I tried re-indexing my table but it did not help.

Thank you,
George.
Avatar of Kevin Hill
Kevin Hill
Flag of United States of America image

I wonder if your Query Analyzer is not committing the transactions.  Wrap your update inside a Begin Tran...Commit
Begin tran
UPDATE owner
SET owner_code=8
WHERE owner_id=123
commit
Avatar of Cosine_Consultants
Cosine_Consultants

ASKER

Tried it and still it does not work.
Do you run the update statement and the second select statement in the same transaction ?

The other sessions will see the updated version of the record only after you issue a commit. If you have explicitly opened a trasaction (bye issuing a "begin tran" statement or if your GUI/tool implicitly opens a transaction (as Toad and Cast Workbench and other tools do when you use default settings), you'll have to commit

Also your second select is
SELECT owner_name
.. shouldn't it be
SELECT owner_code
??
ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The problem is NOT within the Analyser tool because I connect to two different databases and one works and the other does not. Even in the database that I have the problem, this problem is only occuring on specific tables - my belief is that the tables are probably corrupted. Is there a way to test this assumption?
BTW,
my comments above build on the same idea than Kevin3NF's  (sorry Kevin3NF I missed your posts), so in case they help in any way, please give him all the credit.
please try

DBCC CHECKTABLE ('yourtable')
Are there any triggers on this table that may be rolling back your UPDATE?  Have you run a profiler trace during the UPDATE to see what is actually being sent?
thx  hilaire....but I don't get worried about the poinks :-)  Just want to see his Update work
I run the DBCC and I did not get any errors.

The other strange thing is that on the same table for different rows it works, but for other rows it does not.
>> I get a message that my "Command executed successfully" <<

You are getting this message instead of (1 row(s) affected), right?  Looks like you have a trigger on your table.
Avatar of Anthony Perkins
>>Looks like you have a trigger on your table.<<
Most definitely.
Still waiting for these:

Comment from Kevin3NF
Date: 05/11/2005 08:33AM PDT
      Your Comment       

Are there any triggers on this table that may be rolling back your UPDATE?  
Have you run a profiler trace during the UPDATE to see what is actually being sent?


Try this - putting the 8 in single quotes.

UPDATE owner
SET owner_code='8'
WHERE owner_id=123

There are no triggers on the table. I already checked that.
I am going to run the 4th service pack of SQL server. I read that it fixed a lot of UPDATE command bugs.
I just finished with Service Pack 4. The problem is fixed. It was a database bug.

Thank you all for your help.

George.
>>It was a database bug.<<
Can you post the KB article that describes the problem.  You should be able to find it in the list of Fixed items in SP4
Right, I did not think you could.