March 26, 2008 at 12:22 pm
I'm trying to construct a repository for the all stored procedures executed in the system. Essentially I just want to capture the text of a stored procedure and dump it to a table. I know I can do this with sys.dm_exec_sql_text(sql_handle) but the problem using this is it does not fill in the parameter values with the values passed from the stored procedure.
Do I have to do this manually in the procedure or is there already something that catches this that is visible such as the actual text when the transaction is committed.
So for example, this will give the text that was currently executed but @val1 and @val2 are in the text instead of what was passed to mySproc procedure. I would rather not do tons of REPLACE functions to get it where I need to be. Is there a more elegant way to audit this?
create procedure mySproc
@val1 int,
@val2 varchar(50)
as
insert into tabl1(val1,val2)
values(@val1,@val2)
DECLARE @sql_handle-2 varbinary(64)
DECLARE @AuditedText varchar(max)
SELECT @sql_handle-2 = sql_handle FROM sys.dm_exec_requests WHERE sql_handle IS NOT NULL
SELECT @AuditedText = [text]
FROM sys.dm_exec_sql_text(@sql_handle)
March 27, 2008 at 6:19 am
It sure sounds like you need to set up Profiler. That will capture the stored procedure calls along with the parameters passed, no extra code on your part required. You should capture the data out to a file and then import the file into tables for manipulation because it's faster writing out to file than to a table, which will make the Profiler less intrusive in your system.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 27, 2008 at 2:13 pm
Profiler is usually the way to go on this kind of thing.
If what you're doing is some sort of audit data and it will need to run indefinitely, then adding specific code to the procs you want to audit and dumping the param values into an audit table might be better. That way, you only get the data you're looking for, and you aren't having to constantly manage Profiler files.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply