1

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
mismas
  • 1,236
  • 5
  • 27
  • 55
  • Consider doing some work yourself. It is obvious that ther are 2 commands issues. FinTrace them - with the profiler - and check whether they are what you think they are. – TomTom May 28 '14 at 09:24
  • Ok, thank you @TomTom for your reply. Do you have any profiler tool to recommend for MS SQL Server database? Or is it something that comes with db or maybe DBVisualizer which I am not aware of? – mismas May 28 '14 at 09:30
  • Well, it is something that comes WITH SQL SERVER. Why ignore all the tools you already have.... – TomTom May 28 '14 at 09:31
  • I am not ignoring it, I just wasn't aware of it, since I didn't use it before. Also, google-ing it, I have noticed that msdn says they will deprecated it in the future. I don't understand why do you have such condescending tone? Do you want to help or play smart? – mismas May 28 '14 at 09:34
  • Zero tolerance politics towards people not bothering learning what they do - by reading the documentation. Simple like that. – TomTom May 28 '14 at 09:36
  • I disagree that asking this question was 'not bothering learning what I do'. I do RTFM. This question was asked as I didn't know what to do in this case, since it seems an odd behavior to me and I was asking for advice from people who maybe already encountered the same problem or have perhaps more experience with databases than me. Trying to disrespect the other person who is willing to learn something, no matter what, is very lame IMHO. – mismas May 28 '14 at 09:44
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54607/discussion-between-mismas-and-tomtom). – mismas May 28 '14 at 09:49
  • @TomTom please put your comment as an answer, so I can give you credit for it. Thank you. – mismas May 28 '14 at 14:54

2 Answers2

0

Consider doing some work yourself. It is obvious that there are 2 commands issues. Fist Trace them - with the profiler - and check whether they are what you think they are.

SQL Server comes with a decent profiler out of the box. Old rule when you do stuff like that: NEVER assume, always validate. The statements may not even be the same... as long as you do not know that.... all is a vild guess.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Here is something openly condescending for you: WOW! Incredible, what a vanity fair this is ... think about it a little bit for a while ... – mismas May 28 '14 at 19:27
0

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.

mismas
  • 1,236
  • 5
  • 27
  • 55