October 3, 2007 at 8:03 am
I have noticed more than several times that when I try to run an UPDATE statement it will show in the results pane "(1) row affected" for each record that gets updated, as opposed to just saying "(15) rows affected" (assuming only 15 records were touched). Has anyone every experienced this before?
October 3, 2007 at 8:14 am
you get a rows affected for every sql statement performed. that means select,update,insert or delete statements each return the rows affected.
sounds like you had either a cursor or loop that was affecting one row at a time, instead of a single update statement that affected mulitple rows, or maybe some select statements and then an update.
Lowell
October 3, 2007 at 8:17 am
It may be the way your update is written. Could you post the code you are running for us to look at?
😎
October 3, 2007 at 8:50 am
Thanks for taking the time to reply.
In this example, 63 records should be updated, however the results show that each record is updated (and reported on separately). The very last line of the results says (63 row(s) affected):
Example SQL:
UPDATE LUS_CURR_PERD_RATE
SET ACNT_FROM = 'x'
WHERE PERD = 0
Results:
----------------------------------------------------------
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
......... 53 more lines of (1 row(s) affected) .........
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(63 row(s) affected)
October 3, 2007 at 9:16 am
You have a trigger(s) that update rows one at the time.
October 3, 2007 at 9:22 am
How would I confirm that it is actually a trigger that is causing this to happen? I am assuming I could try running sp_helptrigger LUS_CURR_PERD_RATE as a starting point, right?
October 3, 2007 at 9:32 am
One thing that is odd though, is that there is another table which has the exact same structure and atrributes as the table I used in this example (with different data) that does not show "(1) row affected" for each record that gets updated.
October 3, 2007 at 9:38 am
Yup 🙂 That'll give you the names of any triggers on the table. Once you have the names, you can use sp_helptext to get the text of the trigger.
If you can, post it here. There are some people around here who get a bit of a kick out of converting cursor-based code to set-based code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2007 at 9:40 am
Yup... there are already at least three of us waiting for the code to cure it :hehe:.
October 3, 2007 at 9:48 am
Nick Via (10/3/2007)
One thing that is odd though, is that there is another table which has the exact same structure and atrributes as the table I used in this example (with different data) that does not show "(1) row affected" for each record that gets updated.
Nothing odd there at all. Triggers exist on individual tables. So the table that does show the odd results has a trigger on it and the one that doesn't, doesn't have a trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2007 at 10:14 am
I found that there is a trigger on the table, but unfortunately when I ran sp_helptext on it I got the following message: "The object comments have been encrypted."
So it looks like the trigger definition is encrypted. Which I guess makes sense since the database itself is apart of a commercial software package.
I should also note that the other table that doesn't have this problem has the same trigger, at least in name, on it as well.
I'm not really sure what, if anything, I can do at this point.
October 3, 2007 at 10:19 am
There are decrypting scripts on this site and on google.
Take a backup of the db and restore it on another server. Then run those scripts on tha RESTORED version.
Get the code, post it here. We'll correct the code. Then you can place a complaint on the vendor and I'll send you a link supporting the fact that this is a performance hazard for you server and that they must fix their code.
October 3, 2007 at 10:22 am
October 3, 2007 at 10:35 am
Thanks Ninja's_RGR'us!
I will try to find one of those decryption scripts so I can post the trigger's definition.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply