June 8, 2008 at 8:10 am
hello everybody, first i want to apologize for my bad english i'm not a good english speaker 😛
The thing is:
I have implemented an audit structure by mean of audit triggers generated automatically. So I would like to know what sp has fired the trigger in order to save the sp name in the audit structure or at least to know the outer transaction name from the inside of the trigger.
the only thing i could find is the transaction id in the locks tables, but i cannot join that id with anything else to get the name.
i hope you understand my problem and i thank you all. 😀
June 8, 2008 at 11:40 am
The original SQL command can be obtained using DBCC INPUTBUFFER
You will need to parse out the stored procedure name from various possibilities including:
exec sp_spaceused 'Party' , 'true'
execute sp_spaceused 'Party' , 'true'
exec [sp_spaceused] 'Party' , 'true'
execute [sp_spaceused] 'Party' , 'true'
exec dbo.sp_spaceused 'Party' , 'true'
execute dbo.sp_spaceused 'Party' , 'true'
exec dbo.[sp_spaceused] 'Party' , 'true'
execute dbo.[sp_spaceused] 'Party' , 'true'
and so forth.
[/code]
Create procedure InputBuffer_get_SPName
(@SPName nvarchar(4000) output
as
set nocount on
create table #inputbuffer
(EventType nvarchar(30)
,Parameters integer
,EventInfo nvarchar(4000)
)
insert into #inputbuffer
(EventType,Parameters,EventInfo)
exec ( 'dbcc inputbuffer(@@spid)')
select * from #inputbuffer
select @SPName = EventInfo
from #inputbuffer
-- parse out the stored procedure name
return 0
go
[/code]
SQL = Scarcely Qualifies as a Language
June 8, 2008 at 7:06 pm
Tank you very much!! i'm actually testing this posibility to ensure it works for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply