I am using DbVisualizer 8.0.12 as the client tool towards MS SQL Server 2012 database.
I want to perform simple update:
update table1 set field1=0 where filed2='something';
I expect exactly one row to be updated, since field2 is primary key of table1.
Also, doing a:
select * from table1 where field2='something';
returns exactly one row.
But when executing the update sql, DBVisualizer informs me that there were two updates successfully executed.
11:16:58 [UPDATE - 1 row(s), 0.003 secs] Command processed
11:16:58 [UPDATE - 1 row(s), 0.003 secs] Command processed
... 2 statement(s) executed, 2 row(s) affected, exec/fetch time: 0.006/0.000 sec [2 successful, 0 warnings, 0 errors]
I don't understand why is there two updates performed? Shouldn't there be only one update?
Can anybody please advise? Thank you in advance for any kind of information.
[EDIT]
I have used MS SQL Server profiler, as @TomTom suggested.
And I also ran my SQL update using Microsoft SQL Server Management Studio.
Things I had to turn on for the profiler (and for my needs) were:
1. 'Trace properties > Events Selection > Column Filters > Database name – Like: my_db_name', since we have a lot of db on the server, so in order to trace only my database named 'my_db_name'
2. 'Trace properties > Events Selection > Stored procedures > enable SP:StmtStarting and SP:StmtCompleted', since I wanted to enable trigger trace
It seems that this info message from DBVisualizer is misleading (this happens only for tables that have triggers - in this particular case the trigger inserted data into another table(so called, archive table) on every update). Actually, only one update was done, so all fine there.
Microsoft SQL Server Management Studio shows correct info: 1 update and 1 insert.
Hope this will help someone having similar "problem". @TomTom please put your comment as an answer, so I can give you credit for it. Thank you.
Still, there is one more thing I would like to know about Profiler.
Is there a way you can actually see which rows (in which table) will be updated.
From the information I have above, I can only see that there was one update (so I am assuming it is this one on table1, which I expected). But I would like to see information, something like, in this table: 'tablename' this rows: list of rows will be updated with these values or something like that...
Is this possible with the Profiler?
I have used MS SQL Server profiler, as @TomTom suggested. And I also ran my SQL update using Microsoft SQL Server Management Studio.
Things I had to turn on for the profiler (and for my needs) were: 1. 'Trace properties > Events Selection > Column Filters > Database name – Like: my_db_name', since we have a lot of db on the server, so in order to trace only my database named 'my_db_name' 2. 'Trace properties > Events Selection > Stored procedures > enable SP:StmtStarting and SP:StmtCompleted', since I wanted to enable trigger trace
It seems that this info message from DBVisualizer is misleading (this happens only for tables that have triggers - in this particular case the trigger inserted data into another table(so called, archive table) on every update). Actually, only one update was done, so all fine there. Microsoft SQL Server Management Studio shows correct info: 1 update and 1 insert.
Hope this will help someone having similar "problem". @TomTom please put your comment as an answer, so I can give you credit for it. Thank you.
[EDIT]
@TomTom
Hmmm, maybe not.
I think you had enough time to think about it ...
Your answer wasn't helpfull at all (except the little track of light in the confirmative form of:
"Yes, SQL server has profiler included, DAAAH ..."
with no constructive suggestions of your own and with lot of "being a smarty" guy).
An answer to a question should include some more useful information and concrete guidance if you have it, otherwise, don't be a smartass.
Since I did all the work without your help, I think you don't actually deserve credit for it.
The funny thing about it is that you ACTUALLY think you do.
No comment on that, except that I really have ZERO (0.000000000000000000000 > is it going to change, hmmm, let see ... 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000 ... well, I guess not > that is a little bit of smartass for you :) tolerance politics with smartasses like you.
[END OF EDIT]
Still, there is one more thing I would like to know about Profiler. Is there a way you can actually see which rows (in which table) will be updated. From the information I have above, I can only see that there was one update (so I am assuming it is this one on table1, which I expected). But I would like to see information, something like, in this table: 'tablename' this rows: list of rows will be updated with these values or something like that... Is this possible with the Profiler? Thank you in advance for your time and answers.