June 27, 2010 at 10:25 pm
Hi, I'm trying to write some code to use the default trace. Does anyone have a simple way to return the Trace File number?
The file has the format C:\......\Log\log_123.trc which you can get with the fn_trace_getinfo(0) function, but appart from charindex and substringing is there a way to get the 123 portion out?
Does anyone know if it is stored in a system table somewhere?
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
June 27, 2010 at 11:35 pm
try this ...some what like your requirement
SELECT '''' + cast(value as varchar(2000)) + '''' FROM ::fn_trace_getinfo(0) where property = 2
SELECT loginname, loginsid, spid, hostname, applicationname, servername,
databasename, objectName, e.category_id, cat.name as [CategoryName],
textdata, starttime, eventclass, eventsubclass,
--0=begin,1=commit
e.name as EventName
FROM ::fn_trace_gettable('D:\DBA\MSSQL.2\MSSQL\LOG\log_99.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE databasename = 'TraceDB' AND
objectname IS NULL AND --filter by objectname
e.category_id = 5 --AND --category 5 is objects
-- e.trace_event_id = 46 --trace_event_id: 46=Create Obj,47=Drop Obj,164=Alter Obj
go
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply