February 22, 2007 at 12:59 pm
I'm new to profiler and having trouble filtering out results. I am attempting to set a trace that only returns info when a specific table is Inserted/Updated/Deleted. My problem is that something is inserting to a table but I do not know who/what is doing it. I would like to use Profiler to capture events whenever the table is inserted/deleted from.
When using Profiler, I have tried adding the table name I want to watch to the ObjectName filter and to the TextData filter. However, much is returned that is not related to this table at all.
I considered using a trigger but do not know how to capture the info to identifiy a user and to capture the code that executed against the table.
Any help would be much appreciated.
thanks
February 23, 2007 at 8:08 am
If you want to try the trigger method, look over the following example.
The Log table stores the information you wish to log.
The LogTest table was created just to test the use of the log and to create a trigger for testing purposes.
The triTestLog INSERT trigger logs INSERT statements to the Log table. The trigger uses DBCC INPUTBUFFER to get *some* of the code. It is limited to the first 255 characters. But hopefully, that, along with the user id and time, will be enough for investigative purposes.
--=============================================
--
-- SETUP EXAMPLE
--
--=============================================
DROP TABLE Log
GO
CREATE TABLE Log
(
logId int IDENTITY(1,1) PRIMARY KEY
, activityDate datetime DEFAULT GetDate()
, userid varchar(50) DEFAULT SUser_Sname()
, code varchar(255) NULL
, message varchar(100) NULL
)
GO
DROP TABLE TestLog
GO
CREATE TABLE TestLog
(
id int IDENTITY(1,1)
, mydata int
)
GO
CREATE TRIGGER triTestLog ON TestLog
FOR INSERT
AS
CREATE TABLE #inputBuffer
(
EvType nvarchar(30)
, Parms Int
, EvInfo nvarchar(255)
)
SET NOCOUNT ON
INSERT #inputBuffer EXEC ('DBCC INPUTBUFFER (' + @@spid + ') WITH NO_INFOMSGS')
INSERT Log (message, code) SELECT 'INSERT', EvInfo FROM #inputBuffer
DROP TABLE #inputBuffer
GO
--=============================================
--
-- TEST
--
--=============================================
INSERT TestLog (mydata) VALUES (1)
INSERT TestLog (mydata) VALUES (2)
INSERT TestLog (mydata) VALUES (3)
SELECT * FROM Log
GO
INSERT TestLog (mydata) VALUES (1)
GO
INSERT TestLog (mydata) VALUES (2)
GO
INSERT TestLog (mydata) VALUES (3)
GO
SELECT * FROM Log
GO
February 23, 2007 at 12:39 pm
Wonderful! Thanks mkeast. This is much better than what I was kicking around. I will work with this today.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply