Trigger to return SqlCommand
-
Post accidently edited :(, apologies
-
dbcc inputbuffer (@@spid)
But I think it must be executed by another process... because the last line executed will then become "dbcc inputbuffer (@@spid)"
What bug are you trying to track?
-
The bug is in the application code (not a store proc) and is making bad updates to a table. I need to id the bad code and want a trigger to catch when we get a bad update (negative number).
How do I get the EventInfo value from dbcc inputbuffer into @EventInfo ?
DECLARE @spid int
DECLARE @EventInfo nvarchar(255)
SELECT
[USER_NAME()] = USER_NAME()
,[HOST_NAME ()] = HOST_NAME()
,[host_id()] = host_id()
,[SYSTEM_USER] = SYSTEM_USER
,[app_name()] = app_name()
,[@@spid] = @@spid
SET @spid =@@spid
dbcc inputbuffer (@spid)
-
create table #Command (
EventType nvarchar(100),
parmeters int,
EventInfo nvarchar(255)
 
INSERT INTO #Command
EXEC sp_executesql N'dbcc inputbuffer(@SPID)', N'@SPID int', @SPID = @@SPID
_____________
Code for TallyGenerator -
I don't see how that code helps. I tried this
create table #Command (
EventType nvarchar(100),
parmeters int,
EventInfo nvarchar(255)
)
GO
select 1
GO
INSERT INTO #Command
EXEC sp_executesql N'dbcc inputbuffer(@SPID)', N'@SPID int', @SPID = @@SPID
GO
select * from #command
GO
drop table #command
GO
And didn't get what I was after. If you remove the GO from beneath select 1 then you get the select 1 statement PLUS the insert into #command... stuff.
SQL Profiler is definitely the way to go for this. You can set up filters on it to limit the amount of queries you have to sift through.
-
As an alternative, check out sp_WHO in Books Online and it's undocumented cousin sp_WHO2.
Also, I haven't check how @@PROCID reacts in a trigger but it might be another simple oolie to look into...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
So, what are you after?
What gave you idea to spoil the thing with "GO"?
What do you mean by "insert into #command... stuff"?
_____________
Code for TallyGenerator -
The trigger is working and capturing the sql code that makes the change. Thank you all. I used a table that I truncate at the beginning of each trigger run instead of a temp temp table. I am assuming that it isn't any worse than a temp table.
Is there a better way to get the EventInfo value from dbcc inputbuffer inserted directly into the table logging what I get from each trigger run?
BEGIN
TRUNCATE TABLE inputbuffer_tbl
DECLARE @SPID int
set @SPID = @@SPID
INSERT INTO inputbuffer_tbl
EXEC sp_executesql N'dbcc inputbuffer(@SPID)', N'@SPID int', @SPID
INSERT INTO Product_Inventory_update
...,EventInfo
SELECT
...,(SELECT EventInfo FROM inputbuffer_tbl)
-
Ah... sir... you have created "Death by SQL"... if two processes fire the trigger at the same time, who will win on the input buffer table you've created. Do the same thing but use a temp table instead of the input buffer table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply