Null fields (username, host, etc) returned in a trace!?!

I would be really grateful if someone would help me with this as I'm
really stumped...
I've written a stored procedure to enable me to kick off traces from the
SQL Agent and they run just fine - the problem is that some of the fields
that should have data in them just return null. For example I run one to
audit logins and the debug info I added tells me that the events/columns
below are being traced (it picks up what it should trace from a table I
query in the stored procedure). But when I look at the trace output
columns such as NTUserName, ClientHostName, etc, etc they are always null
(pretty much the only thing that does get populated is TextData, SPID and
ServerName). Interesting the fields don't even appear when I open the
file in Profiler but do show up as null if open the file using
::fn_trace_gettable.
If I run a trace using SQL Profiler then it shows all the fields as it
should! Any help would be much appreciated, and let me know if you want
me to post the full stored procedure. I'm using SQL 2000 on a Windows
2003 server.
To massively summarise the stored procedure, here it is
sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
-- pull the events to be traced from a table
exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
-- pull the columns to be reported on from a table
exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
Here is the debug info I added (just spits out the events and the columns
it traces
Trace Event Info
--
Trace Events for session
(1 rows(s) affected)
EventNumber Category EventName EventDescription
-- -- -- --
14 session Login Occurs when a use
15 session Logout Occurs when a use
20 session Login Failed Indicates that a
(3 rows(s) affected)
Trace Column Info
---
Trace Columns for session
(1 rows(s) affected)
Category ColumnNumber ColumnName ColumnDescription
-- -- -- --
all 1 TextData Text value depen
all 3 DatabaseID ID of the databa
all 6 NTUserName Microsoft Window
all 8 ClientHostName Name of the clie
all 9 ClientProcessID ID assigned by t
all 10 ApplicationName Name of the clie
all 11 SQLSecurityLoginName SQL Server login
all 12 SPID Server Process I
all 13 Duration Amount of elapse
(24 rows(s) affected)
TraceID
--
5
Thanks very much for all your help and apologies if this is a dumb
question but it has me stumped!
DomHi
In profiler the data fields that will appear will depend on the template you
choose. You can add the data columns needed from the subsequent dialog. If
you get the columns/events working in profiler, you can use the Script Trace
on the file menu to give you the SQL needed to re-create that profile.
At a guess you are probably not calling the sp_trace_setevent for the
correct event/column combinations.
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>I would be really grateful if someone would help me with this as I'm
> really stumped...
> I've written a stored procedure to enable me to kick off traces from the
> SQL Agent and they run just fine - the problem is that some of the fields
> that should have data in them just return null. For example I run one to
> audit logins and the debug info I added tells me that the events/columns
> below are being traced (it picks up what it should trace from a table I
> query in the stored procedure). But when I look at the trace output
> columns such as NTUserName, ClientHostName, etc, etc they are always null
> (pretty much the only thing that does get populated is TextData, SPID and
> ServerName). Interesting the fields don't even appear when I open the
> file in Profiler but do show up as null if open the file using
> ::fn_trace_gettable.
> If I run a trace using SQL Profiler then it shows all the fields as it
> should! Any help would be much appreciated, and let me know if you want
> me to post the full stored procedure. I'm using SQL 2000 on a Windows
> 2003 server.
> To massively summarise the stored procedure, here it is
> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
> -- pull the events to be traced from a table
> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
> -- pull the columns to be reported on from a table
> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> Here is the debug info I added (just spits out the events and the columns
> it traces
> Trace Event Info
> --
> Trace Events for session
> (1 rows(s) affected)
> EventNumber Category EventName EventDescription
> -- -- -- --
> 14 session Login Occurs when a use
> 15 session Logout Occurs when a use
> 20 session Login Failed Indicates that a
> (3 rows(s) affected)
> Trace Column Info
> ---
> Trace Columns for session
> (1 rows(s) affected)
> Category ColumnNumber ColumnName ColumnDescription
> -- -- -- --
> all 1 TextData Text value depen
> all 3 DatabaseID ID of the databa
> all 6 NTUserName Microsoft Window
> all 8 ClientHostName Name of the clie
> all 9 ClientProcessID ID assigned by t
> all 10 ApplicationName Name of the clie
> all 11 SQLSecurityLoginName SQL Server login
> all 12 SPID Server Process I
> all 13 Duration Amount of elapse
> (24 rows(s) affected)
> TraceID
> --
> 5
> Thanks very much for all your help and apologies if this is a dumb
> question but it has me stumped!
> Dom|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
> Hi
> In profiler the data fields that will appear will depend on the
> template you choose. You can add the data columns needed from the
> subsequent dialog. If you get the columns/events working in profiler,
> you can use the Script Trace on the file menu to give you the SQL
> needed to re-create that profile.
> At a guess you are probably not calling the sp_trace_setevent for the
> correct event/column combinations.
> John
>
> "Dom" <post.to.group@.newsgroup.com> wrote in message
> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>>I would be really grateful if someone would help me with this as I'm
>> really stumped...
>> I've written a stored procedure to enable me to kick off traces from
>> the SQL Agent and they run just fine - the problem is that some of
>> the fields that should have data in them just return null. For
>> example I run one to audit logins and the debug info I added tells me
>> that the events/columns below are being traced (it picks up what it
>> should trace from a table I query in the stored procedure). But when
>> I look at the trace output columns such as NTUserName,
>> ClientHostName, etc, etc they are always null (pretty much the only
>> thing that does get populated is TextData, SPID and ServerName).
>> Interesting the fields don't even appear when I open the file in
>> Profiler but do show up as null if open the file using
>> ::fn_trace_gettable.
>> If I run a trace using SQL Profiler then it shows all the fields as
>> it should! Any help would be much appreciated, and let me know if you
>> want me to post the full stored procedure. I'm using SQL 2000 on a
>> Windows 2003 server.
>> To massively summarise the stored procedure, here it is
>> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize,
>> NULL -- pull the events to be traced from a table
>> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
>> -- pull the columns to be reported on from a table
>> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
>> -- Set the trace status to start
>> exec sp_trace_setstatus @.TraceID, 1
>> Here is the debug info I added (just spits out the events and the
>> columns it traces
>> Trace Event Info
>> --
>> Trace Events for session
>> (1 rows(s) affected)
>> EventNumber Category EventName EventDescription
>> -- -- --
>> -- 14 session Login
>> Occurs when a use 15 session Logout
>> Occurs when a use 20 session Login Failed
>> Indicates that a
>> (3 rows(s) affected)
>> Trace Column Info
>> ---
>> Trace Columns for session
>> (1 rows(s) affected)
>> Category ColumnNumber ColumnName
>> ColumnDescription -- --
>> -- -- all 1
>> TextData Text value depen all 3
>> DatabaseID ID of the databa all 6
>> NTUserName Microsoft Window all 8
>> ClientHostName Name of the clie all 9
>> ClientProcessID ID assigned by t all 10
>> ApplicationName Name of the clie all 11
>> SQLSecurityLoginName SQL Server login all 12
>> SPID Server Process I all 13
>> Duration Amount of elapse
>> (24 rows(s) affected)
>> TraceID
>> --
>> 5
>> Thanks very much for all your help and apologies if this is a dumb
>> question but it has me stumped!
>> Dom
>
>
Thanks very much for the reply. There is a chance I could be looking at
the wrong columns but the debug I put into my stored procedure shows that
I'm at least adding the column to the trace (NTUserName for instance),
and it does show up as a field in ::fn_trace_gettable which I wouldn't
have thought it would unless the trace is doing something with it... I
just can't figure why it always comes up as null. Is there anyway to get
info from an active trace i.e. which columns are being recorded?
TIA
Dom|||Hi
If you got it working in profiler then scripting it would recreate it the
same and therefore should be no need to debug.
fn_trace_geteventinfo should say what events/data columns you are tracing.
This is what I get when I script the login/logout/login failed events
/****************************************************/
/* Created by: SQL Profiler */
/* Date: 15/05/2005 09:13:32 */
/****************************************************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 7, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
exec sp_trace_setevent @.TraceID, 14, 40, @.on
exec sp_trace_setevent @.TraceID, 14, 41, @.on
exec sp_trace_setevent @.TraceID, 15, 1, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 7, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 40, @.on
exec sp_trace_setevent @.TraceID, 15, 41, @.on
exec sp_trace_setevent @.TraceID, 20, 1, @.on
exec sp_trace_setevent @.TraceID, 20, 6, @.on
exec sp_trace_setevent @.TraceID, 20, 7, @.on
exec sp_trace_setevent @.TraceID, 20, 9, @.on
exec sp_trace_setevent @.TraceID, 20, 10, @.on
exec sp_trace_setevent @.TraceID, 20, 11, @.on
exec sp_trace_setevent @.TraceID, 20, 12, @.on
exec sp_trace_setevent @.TraceID, 20, 13, @.on
exec sp_trace_setevent @.TraceID, 20, 14, @.on
exec sp_trace_setevent @.TraceID, 20, 16, @.on
exec sp_trace_setevent @.TraceID, 20, 17, @.on
exec sp_trace_setevent @.TraceID, 20, 18, @.on
exec sp_trace_setevent @.TraceID, 20, 40, @.on
exec sp_trace_setevent @.TraceID, 20, 41, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:zOthe.3865$Pi3.3156@.newsfe4-win.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in
> news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
>> Hi
>> In profiler the data fields that will appear will depend on the
>> template you choose. You can add the data columns needed from the
>> subsequent dialog. If you get the columns/events working in profiler,
>> you can use the Script Trace on the file menu to give you the SQL
>> needed to re-create that profile.
>> At a guess you are probably not calling the sp_trace_setevent for the
>> correct event/column combinations.
>> John
>>
>> "Dom" <post.to.group@.newsgroup.com> wrote in message
>> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>>I would be really grateful if someone would help me with this as I'm
>> really stumped...
>> I've written a stored procedure to enable me to kick off traces from
>> the SQL Agent and they run just fine - the problem is that some of
>> the fields that should have data in them just return null. For
>> example I run one to audit logins and the debug info I added tells me
>> that the events/columns below are being traced (it picks up what it
>> should trace from a table I query in the stored procedure). But when
>> I look at the trace output columns such as NTUserName,
>> ClientHostName, etc, etc they are always null (pretty much the only
>> thing that does get populated is TextData, SPID and ServerName).
>> Interesting the fields don't even appear when I open the file in
>> Profiler but do show up as null if open the file using
>> ::fn_trace_gettable.
>> If I run a trace using SQL Profiler then it shows all the fields as
>> it should! Any help would be much appreciated, and let me know if you
>> want me to post the full stored procedure. I'm using SQL 2000 on a
>> Windows 2003 server.
>> To massively summarise the stored procedure, here it is
>> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize,
>> NULL -- pull the events to be traced from a table
>> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
>> -- pull the columns to be reported on from a table
>> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
>> -- Set the trace status to start
>> exec sp_trace_setstatus @.TraceID, 1
>> Here is the debug info I added (just spits out the events and the
>> columns it traces
>> Trace Event Info
>> --
>> Trace Events for session
>> (1 rows(s) affected)
>> EventNumber Category EventName EventDescription
>> -- -- --
>> -- 14 session Login
>> Occurs when a use 15 session Logout
>> Occurs when a use 20 session Login Failed
>> Indicates that a
>> (3 rows(s) affected)
>> Trace Column Info
>> ---
>> Trace Columns for session
>> (1 rows(s) affected)
>> Category ColumnNumber ColumnName
>> ColumnDescription -- --
>> -- -- all 1
>> TextData Text value depen all 3
>> DatabaseID ID of the databa all 6
>> NTUserName Microsoft Window all 8
>> ClientHostName Name of the clie all 9
>> ClientProcessID ID assigned by t all 10
>> ApplicationName Name of the clie all 11
>> SQLSecurityLoginName SQL Server login all 12
>> SPID Server Process I all 13
>> Duration Amount of elapse
>> (24 rows(s) affected)
>> TraceID
>> --
>> 5
>> Thanks very much for all your help and apologies if this is a dumb
>> question but it has me stumped!
>> Dom
>>
> Thanks very much for the reply. There is a chance I could be looking at
> the wrong columns but the debug I put into my stored procedure shows that
> I'm at least adding the column to the trace (NTUserName for instance),
> and it does show up as a field in ::fn_trace_gettable which I wouldn't
> have thought it would unless the trace is doing something with it... I
> just can't figure why it always comes up as null. Is there anyway to get
> info from an active trace i.e. which columns are being recorded?
> TIA
> Dom