April 26, 2016 at 3:36 pm
I created a trace for 3 events:
46 - Object:Created
47 - Object:Deleted
164 - Object:Altered
Here is my code block just for event 46 - Object created
declare @on bit
set @on = 1
-- create:
exec sp_trace_setevent @TraceID, 46, 1, @on -- text data
exec sp_trace_setevent @TraceID, 46, 3, @on -- db id
exec sp_trace_setevent @TraceID, 46, 4, @on -- txn id
exec sp_trace_setevent @TraceID, 46, 6, @on -- NTuserName
exec sp_trace_setevent @TraceID, 46, 7, @on -- NTdomainName
exec sp_trace_setevent @TraceID, 46, 8, @on -- host name
exec sp_trace_setevent @TraceID, 46, 10, @on -- app name
exec sp_trace_setevent @TraceID, 46, 11, @on -- login
exec sp_trace_setevent @TraceID, 46, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 46, 14, @on -- begin
exec sp_trace_setevent @TraceID, 46, 15, @on -- end
exec sp_trace_setevent @TraceID, 46, 27, @on -- event class
exec sp_trace_setevent @TraceID, 46, 28, @on -- object type
exec sp_trace_setevent @TraceID, 46, 31, @on -- error
exec sp_trace_setevent @TraceID, 46, 34, @on -- object name
exec sp_trace_setevent @TraceID, 46, 35, @on -- database name
exec sp_trace_setevent @TraceID, 46, 64, @on -- SessionLoginName
I select it with the following query:
select
textdata,
databaseid,
transactionid,
NTuserName,
NTdomainName,
hostname,
applicationname,
loginname,
SPID,
StartTime,
EndTime,
eventclass,
objecttype,
error,
objectname,
databasename,
SessionLoginName
from sys.fn_trace_gettable('MY_Path_File.trc', default)
However, it returns NULL for TextData column. Why? What I should change to get it ?
Thanks
April 26, 2016 at 3:56 pm
Text data is not a column returned for those events.
See https://technet.microsoft.com/en-us/library/ms190194(v=sql.110).aspx for the columns returned by the various Objects Events.
If you want to see the T-SQL command associated, you'll need to pull in other events.
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply