November 9, 2006 at 6:55 am
I use profiler. It is a powerfull tool, but I cann't find what I want. Tried to make the filter regarding to ObjectID, but it seems it doesn't work- I get a huge list of various records through the entire DB(filtering Database ID works, but that's not enough for me). The thing is that I need to identify all SP which updates one particular column in the x table. Is it possible?
Thanks
November 9, 2006 at 7:59 am
You can use an UPDATE trigger, and send the update event to your SQL errorlog.
November 9, 2006 at 12:09 pm
I couldn't agree with you. Trigger could be the best solution but how to get the procedure name, that updated the records? That appears the main problem to me for now
November 9, 2006 at 2:15 pm
How about use the filter in the SQL Profiler?
November 10, 2006 at 2:30 am
1) Run the SQL profiler ( choose template or create one yourself )
2) save the tracle to a file
3) Open the trace you just saved and filter the trace
for a more permanent logging
1) use a trigger on the X table and log it into a reporting table
2) alter the scripts which are actual updating the X table and log the reporting table from there
3) DO NOT forget to clean up the reporting table every now and then ( keep a history for 90 days )
Regards,
GKramer
The Netherlands
November 10, 2006 at 5:15 am
Ok, from yours suggestions I see that there are two possible ways to accomplish my goal:
1. Trigger
2. Profiler
I've tried these both ways, but the drawback of the first is that I couldn't find the way to show me the procedure name which updated the record. Do you know how to do that?
Talking about Profiler, it generates a lot of results from entire Database. I choosed Event: Stored ProceduresP:SmtCompleted(as I remember), and the column Textdata shows the SP syntax. From there I can filter the queries that updated my x table. But I think this is not the best way.
Does anyone know, what events, columns and filters I should take into my x table audit trace?
Thanks a lot
November 10, 2006 at 5:23 am
there's actually a different approach if your app is all sp driven and you just want to know which procs access your table - do a search in syscomments.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 10, 2006 at 5:40 am
Colin,
are you telling me that the syscomments table will tell me what procedures had updated the x table particular column?
I have not a possibility to take a look at the yours suggested table now... But if you correctly understood me and you say the truth I would be very appreciate to you for the such info
November 10, 2006 at 12:48 pm
You can capture the SQL that caused a trigger to fire. I forget exactly the layout for the logging table, but if you play in your sandbox, you should be able to figure it out.
Create trigger foo on bar
For Update,Insert,Delete
as
Insert Forensic.dbo.LoggingTableforTrigger_bar
EXEC ('DBCC Inputbuffer (@@SPID)')
GO
Now, when the trigger on the table [bar] is fired, the currently-executing code (That caused the trigger to fire) gets logged in the table.
hth jg
November 12, 2006 at 12:15 pm
Jeff,
you are genius! Thank you very much.
Good Luck!
Vidas
November 13, 2006 at 6:24 am
Vidas,
I should mention that putting that kind of trigger in a production environment could be dangerous, so use it with caution.
For example, if the table gets updated by a procedure that uses EXEC ('SomeSQLStatement'), the trigger will fail due to nested EXECs. This could have some unintended consequences.
jg
November 13, 2006 at 7:02 am
Jeff,
Thanks about the warning. The worst thing that could happen is that some nested procedure will not be noticed. I think the main goal is to identify the primary procedures, and the others(nested) will be discovered manually.
For the present I'm satisfied with results. Thanks again
Vidas
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply