Showing posts with label trace. Show all posts
Showing posts with label trace. Show all posts

Sunday, March 25, 2012

Creating a trace file with the date appended to its name

I'm trying to create a trace file with the file as part of it's name.
For example, I'd like to create a file called FailedLogins-20050428. So
far I haven't been able to figure out how to get the name of the file
and the date together (I'm sill very new to SQL Server and tracing).

What I've done is:
declare @.rc int
declare @.traceid int
declare @.maxfilesize bigint
set @.maxfilesize = 50
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins', @.maxfilesize=@.maxfilesize,
@.stoptime=NULL
if @.rc > 0 print 'sp_trace_code failed with error code ' +
rtrim(cast(@.rc as char))
else print 'traceid for the trace is ' + rtrim(cast(@.traceid as char))

I can create a trace file on C drive without difficulty. I've tried
creating a file like this:
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins + convert (varchar,getdate(),112',
@.maxfilesize=@.maxfilesize, @.stoptime=NULL

But what I end up created is a file on C called
failedlogins + convert(varchar,getdate(),112).trc

I have no doubt what I want to do can be done. I just done know how to
do it.

If anyone could tell me where I'm going wrong, I'd really appreciate
it.

Thanks in advance.Hiya Bill,

Try this in Query Analyzer.. @.tracefile is your variable,
@.tracefile_new is the proposed fix. Take note of the @.tracefile_new
output..

declare @.tracefile varchar(1000),
@.tracefile_new varchar(1000)

set @.tracefile=N'C:\trace\failedlogins + convert
(varchar,getdate(),112'
set @.tracefile_new=N'C:\trace\failedlogins_' + convert
(varchar,getdate(),112)

print @.tracefile

print @.tracefile_new|||Thanks Greg! It works great. Just the way I wanted it.

creating a trace definition file (TDF) via code

Is there any way (either TSQL/SMO) to create a new TDF file, I'm using the traceserver InitializeAsReader sub, so need to pass it a reference to a TDF file, and I'd like to control what it traces via the TDF rather than parsing the TextData of the output.

Cathal

There is a Trace API that ships with SMO (see the Microsoft.SqlServer.Management.Trace namespace).

See the TraceServer.InitializeAsReader() method. You can specify a predefine trace template as the second parameter and then start the trace with this template.

|||

Thanks Michiel, but I'm actually using that method already, my question is on the creation of the TDF itself.

Ideally I'd like a t-sql/smo script to generate a TDF template, so I could dynamically determine what events to track and also filter for particular users/databases e.g. if I was only interested in tracking northwind events, I'd create a template based on TSQL_SPs, but that also used a DatabaseName column filter set to like '%Northwind%' . Obviously i can do this manually, but I would like to automate this step for an application I'm writing. I suspect it's not possible to serialize a trace definition to a TDF file. I can use TSQL to create a trace with the correct filters I require, but as I can't pass a running trace to InitializeAsReader, I can't use it. At present I've assumed I'll have to filter the eventData via code, but I thought I'd ask just in case.

Cathal

|||This is not possible, AFAIK. I'll make sure this gets logged as a feature request.

Friday, February 17, 2012

Createing a trace to load a table

Hi All,
Is there a programmatic way to create a trace that loads a table?
I created a trace that loads a table; I started it and stopped it. I
scripted the trace to SQL 2000, and tried to start the trace with the SQL
code created by the profiler in query Analyzer. I have been running these
where they load files with no problems.
I added the table name to procedure where it tells me to add a file name but
the table does not load.
Is this possible? Running to file first leaves me open for SOX to say this
is not fully a live audit, there will be up to an hour depending how fast the
files load.
TIA,
Joe
jaylou wrote:
> Hi All,
> Is there a programmatic way to create a trace that loads a table?
> I created a trace that loads a table; I started it and stopped it. I
> scripted the trace to SQL 2000, and tried to start the trace with the
> SQL code created by the profiler in query Analyzer. I have been
> running these where they load files with no problems.
> I added the table name to procedure where it tells me to add a file
> name but the table does not load.
> Is this possible? Running to file first leaves me open for SOX to
> say this is not fully a live audit, there will be up to an hour
> depending how fast the files load.
> TIA,
> Joe
There is no way to trace to a table directly with a guarantee that all
events will be captured. You need to do what you are already doing. That
is, trace to a flat file on the server itself and then migrate that
trace file information to a trace table if needed once the trace
completes. The file loads real time. It's just that you can't query the
file while SQL Server has it open. Once the trace completes or the trace
does a rollover to a second file, you can query the first.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||thanks again David.
"David Gugick" wrote:

> jaylou wrote:
> There is no way to trace to a table directly with a guarantee that all
> events will be captured. You need to do what you are already doing. That
> is, trace to a flat file on the server itself and then migrate that
> trace file information to a trace table if needed once the trace
> completes. The file loads real time. It's just that you can't query the
> file while SQL Server has it open. Once the trace completes or the trace
> does a rollover to a second file, you can query the first.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>