Values not updating but showing rows affected

Question

THERE IS NO TRIGGER ON THIS TABLE.

I'm facing strange behaviour of sql server. One of the table column value not updating.

Here is the query and output:

Select statement

Now if I execute the update statement, it executes successfully:

Update statement

As per update statement all the clientId values should be 10, but it still remains 2. Here the select query result after executing update statement:

Select statement

I really don't find any possible issue of this behaviour. Please help to solve this puzzle.

This might helpfully to help me:

SQL Server 2012 Express
Table:

Table visual info

Schema: Table schema
If I rename the column name clientId to clientId2 or anything then update works. But if I rename the changed column name to clientId with udpated value then the updated values become 2 again.
If I keep the columne name same but table name change to Company2 or anything then then clientId values update fine.
https://raw.githubusercontent.com/codenamejakir/Demo-Video/master/sqldemo.swf
With transaction: https://raw.githubusercontent.com/codenamejakir/Demo-Video/master/SqlLive.swf

Overall I have noticed that if the table name "Company" and column name "ClientId" then column value not updating.

Thanks.

No trigger set on this table, have a look: https://i.stack.imgur.com/SXQNZ.png — Jakir Hossain, Sep 24 '18 at 11:29
Welcome to StackOverflow! Could you please [edit] your question and add the images as text? It will make it easier to read for us. — MBijen, Sep 24 '18 at 11:33
I've executed the update statement withing Transaction scope but doesn't help. — Jakir Hossain, Sep 24 '18 at 11:48
Did you run update statement as transaction? if yes then commit it to see the results? To know open transactions run DBCC opentran. — Tharunkumar Reddy, Sep 24 '18 at 12:04
(Once it happened to me, so you might try) Drop existing indexes and recreate them (maybe do on a backup). — Cetin Basoz, Sep 24 '18 at 12:13
No idea, your video is very interesting (and Ilike that tool for showing your work, awesome). It is like you're stuck in some automatic rollback situation. So when you run both at the same time, its putting them as one transaction and rolling it back at the end. Never seen anything like that before. You dont have any third party add-ons or anything? — PreQL, Sep 24 '18 at 12:21
Could you run `SELECT SQLTEXT.text, STATS.last_execution_time FROM sys.dm_exec_query_stats STATS CROSS APPLY sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT WHERE STATS.last_execution_time > GETDATE()-1 ORDER BY STATS.last_execution_time DESC` to see if there have been any queries ran on the background after you do the update — MBijen, Sep 24 '18 at 12:28
@PreQL I have tested in another pc, no 3rd party tool, same issue. — Jakir Hossain, Sep 24 '18 at 15:39
Try [`SET IMPLICIT_TRANSACTIONS OFF`](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql), then do the update. — Richardissimo, Sep 24 '18 at 19:39
Finally the ghost is out, it was caused by a windows service that always execute the update statement behind, I created it long ago testing perpose and forgot to stop the service. :P Thanks all of you for your great help. — Jakir Hossain, Sep 25 '18 at 03:54

Values not updating but showing rows affected

0 Answers0