Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cerin
Starting Member
7 Posts |
 Posted - 2008-11-05 : 10:32:40
|
| I have an awkward problem. I'm debugging what I believe is a race condition in a legacy codebase. The issue involves a userID column in a table being set to the wrong value. Essentially, one user's record will be non-deterministically reassigned to another user. The real problem is that this column is set from several different stored procedures, and there's no logging, so I don't even know where to start.I was thinking of setting up an update trigger on the column, and log the "to" and "from" values, but this doesn't really help me if I don't know what procedure's doing the update. Is there anyway to log the procedure doing the update from inside the trigger? |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-05 : 11:00:16
|
| Auditing stored procedure execution:http://weblogs.sqlteam.com/brettk/archive/2006/09/21/12391.aspx |
 |
|
|
Cerin
Starting Member
7 Posts |
Posted - 2008-11-05 : 11:24:32
|
| I don't see how that post is relevant. How do you use that proc to log the change to a single column? Are you supposed to call that logging proc inside every target stored procedure? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-05 : 11:27:48
|
| Is userID an autoincrement field? is the assignment done through procedure itself? |
|
|
|
Cerin
Starting Member
7 Posts |
Posted - 2008-11-05 : 11:39:25
|
| visakh16,The column is a varchar of all things. The assignment is done from 6 different stored procedures. All the procedures seem to be passed this value, so the problem likely lies elsewhere, but I'd still like to log their use to better track down what direction that bad data is coming from.I tried doing OBJECT_NAME(@@PROCID) inside the trigger, but this just captures the trigger's name, not the name of the stored procedure that triggered the trigger. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-05 : 11:43:59
|
| so all these 6 procedure will pass same value you mean? Where exactly is insertion of details happening with generation of UseriD value? |
|
|
|
Cerin
Starting Member
7 Posts |
Posted - 2008-11-05 : 12:37:04
|
| Yes, all the procs receive userId as an argument, and update the column. The initial insertion of the userId is irrelevant. |
|
|
|
Cerin
Starting Member
7 Posts |
Posted - 2008-11-06 : 12:38:19
|
| Nevermind, I figured out how to do it. Turned out to be a lot easier than I thought. |
|
|
|
|
|
|
|
|
Logging Stored Procedure from Trigger