• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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.
0
Cosine_Consultants
Asked:
Cosine_Consultants
  • 6
  • 5
  • 4
  • +3
1 Solution
 
Kevin3NFCommented:
I wonder if your Query Analyzer is not committing the transactions.  Wrap your update inside a Begin Tran...Commit
0
 
Kevin3NFCommented:
Begin tran
UPDATE owner
SET owner_code=8
WHERE owner_id=123
commit
0
 
Cosine_ConsultantsAuthor Commented:
Tried it and still it does not work.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
HilaireCommented:
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
??
0
 
HilaireCommented:
Please issue a

SELECT @@TRANCOUNT

in the current session
0
 
Cosine_ConsultantsAuthor Commented:
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?
0
 
HilaireCommented:
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.
0
 
HilaireCommented:
please try

DBCC CHECKTABLE ('yourtable')
0
 
Kevin3NFCommented:
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?
0
 
Kevin3NFCommented:
thx  hilaire....but I don't get worried about the poinks :-)  Just want to see his Update work
0
 
Cosine_ConsultantsAuthor Commented:
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.
0
 
rafranciscoCommented:
>> 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.
0
 
Anthony PerkinsCommented:
>>Looks like you have a trigger on your table.<<
Most definitely.
0
 
Kevin3NFCommented:
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?


0
 
JulianvaCommented:
Try this - putting the 8 in single quotes.

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

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

Thank you all for your help.

George.
0
 
Anthony PerkinsCommented:
>>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
0
 
Anthony PerkinsCommented:
Right, I did not think you could.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now