Problem with Check Constraints

I am working with an evaluation copy of SQL Server 2000 for the first
time; my DB experience lies with MS Access.

I have a simple table in SQL Server (tblCompany) that has a field
called "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.

In SQL Server Design Table view, I've clicked on "Manage Constraints"
and put the following code in that I found elsewhere:

([Ticker] = upper([Ticker]))

I then checked all three boxes below: "Check existing data on
creation," "Enforce constraint for replication," and "Enforce
constraint for INSERTs and UPDATEs." The first one, "Check existing
data..." is checked as I've already entered in some data in the field
in lowercase to see if the check constraint would go back and change
it to Upper Case--this because I'm wanting to ultimately migrate a
table from Access to SQL Server and ensure that all Tickers are in
Upper Case.

I'm able to do this and then save the table design with changes; but
every time, I then go and look at the table data to see if the check
constraint was applied, and each time it is not; then, I go back to
"Manage Constraints" and find that the "Check existing data..." box is
unchecked. I've gone through this SEVERAL times.

Hoping this is something simple. Apologize for my "newbieness." I've
got a "For Dummies" book in front of me as well as numerous Internet
windows open, trying to figure this out. Have checked books online on
the MSFT site as well to no avail.

Thanks in advance--

RADA constraint enforces data integrity rules but does not change existing or
newly inserted data. You need to cleanup your data and then add the
constraint to only permit uppercase values going forward.

If you want to automatically change values to upper case as they are entered
on the server side, you'll need to do this in a trigger. IMHO, this task is
better done in application code and let the database just enforce the data
integrity rule.

I don't know the details of the constraint you are adding but be aware that
case sensitivity is determined by collations in SQL 2000. The default
collation is case insensitive so you'll need to override the default
case-insensitive compare in your constraint. The example script below will
correct existing data and add a check constraint to ensure only upper case
values are allowed.

UPDATE Company
SET Ticker = UPPER('Ticker')
GO

ALTER TABLE Company
ADD CONSTRAINT CK_Ticker
CHECK (Ticker COLLATE SQL_Latin1_General_Cp1_CS_AS = UPPER(Ticker))
GO

On a side note, be aware that Hungarian notation (e.g. 'tbl' prefixes) are
frowned upon in client-server database design. The underlying database
implementation (table or view) should be transparent to database users.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RAD" <rdavenport@.nyc.rr.com> wrote in message
news:d4119d8.0401251017.28f0b46f@.posting.google.co m...
> I am working with an evaluation copy of SQL Server 2000 for the first
> time; my DB experience lies with MS Access.
> I have a simple table in SQL Server (tblCompany) that has a field
> called "Ticker." When new company stock tickers (i.e., MSFT for
> Microsoft) are entered into the field, I'd like them in all
> caps--whether the user types msft, Msft, MsFt, etc. In Access, this
> was easy--simply set the Format to ">" in table design view.
> In SQL Server Design Table view, I've clicked on "Manage Constraints"
> and put the following code in that I found elsewhere:
> ([Ticker] = upper([Ticker]))
> I then checked all three boxes below: "Check existing data on
> creation," "Enforce constraint for replication," and "Enforce
> constraint for INSERTs and UPDATEs." The first one, "Check existing
> data..." is checked as I've already entered in some data in the field
> in lowercase to see if the check constraint would go back and change
> it to Upper Case--this because I'm wanting to ultimately migrate a
> table from Access to SQL Server and ensure that all Tickers are in
> Upper Case.
> I'm able to do this and then save the table design with changes; but
> every time, I then go and look at the table data to see if the check
> constraint was applied, and each time it is not; then, I go back to
> "Manage Constraints" and find that the "Check existing data..." box is
> unchecked. I've gone through this SEVERAL times.
> Hoping this is something simple. Apologize for my "newbieness." I've
> got a "For Dummies" book in front of me as well as numerous Internet
> windows open, trying to figure this out. Have checked books online on
> the MSFT site as well to no avail.
> Thanks in advance--
> RAD|||On 25 Jan 2004 10:17:18 -0800, rdavenport@.nyc.rr.com (RAD) wrote:

>I am working with an evaluation copy of SQL Server 2000 for the first
>time; my DB experience lies with MS Access.
>I have a simple table in SQL Server (tblCompany) that has a field
>called "Ticker." When new company stock tickers (i.e., MSFT for
>Microsoft) are entered into the field, I'd like them in all
>caps--whether the user types msft, Msft, MsFt, etc. In Access, this
>was easy--simply set the Format to ">" in table design view.
>In SQL Server Design Table view, I've clicked on "Manage Constraints"
>and put the following code in that I found elsewhere:
>([Ticker] = upper([Ticker]))
>I then checked all three boxes below: "Check existing data on
>creation," "Enforce constraint for replication," and "Enforce
>constraint for INSERTs and UPDATEs." The first one, "Check existing
>data..." is checked as I've already entered in some data in the field
>in lowercase to see if the check constraint would go back and change
>it to Upper Case--this because I'm wanting to ultimately migrate a
>table from Access to SQL Server and ensure that all Tickers are in
>Upper Case.
>I'm able to do this and then save the table design with changes; but
>every time, I then go and look at the table data to see if the check
>constraint was applied, and each time it is not; then, I go back to
>"Manage Constraints" and find that the "Check existing data..." box is
>unchecked. I've gone through this SEVERAL times.
>Hoping this is something simple. Apologize for my "newbieness." I've
>got a "For Dummies" book in front of me as well as numerous Internet
>windows open, trying to figure this out. Have checked books online on
>the MSFT site as well to no avail.
>Thanks in advance--
>RAD
That doesn't work, as can be shown with

select ticker from tblCompany where ticker = 'MSFT'

Your row will be returned regardless of the case of the data.

this may be something that can be set at the database level,
alternatively use a trigger to uppercase the data.

Something like;

create trigger instblCompany
on tblCompany
instead of insert
as
insert into tblCompany
select upper(ticker), all other columns
from inserted|||Thanks both Dan and Lyndon--I'll give it a whirl.

RAD

Problem with arithmetical calculations

Hello, everyone.

I have some experience working with T-SQL, but now I am faced with problem which I never seen before.

I have query:

SELECT2/(9+1)*1000

This query returns 0, but it must return 200.

I have tried this in SQL 2000 and SQL 2005, but result is the same.

What is wrong?

B.R.

Girts

I have found solution for this problem - it looks very simple and stypid :

SELECT 2/(9*1.0+1)*1000

|||

It's very simple, yes, but stupid? Not at all.

Have you thought about what you're calculating?
How much is 2 divided by 10?
How would one write 0.2 using only integers..? You can't, so it's rounded down to 0.

So your real problem is not how it's calculated, but what datatypes are used to do the calculation. When you add the decimalpoint as you found out, it's no longer done by all integers, but with decimals instead, so the rounding doesn't have to happen.
Thus you get the expected result. =:o)

You can find out more in BOL if you look for 'datatypes' and 'datatype precedence'

=;o)
/Kenneth

|||

Well, this is kind of wierd looking (the bold):

SELECT 2/(9*1.0+1)*1000

But like KeWin is saying it does the trick because it causes the 1.0 to be converted to a numeric value. The same effect can be achieved by:

SELECT 2.0/(9+1)*1000

You really have to be careful to consider the datatype at each step of the evaluation of the equation.

Integer + Integer = Integer
Integer / Integer = Integer

select 1/2 = 0 --because in integer math, you truncate the result, not round
select 1%2 = 1 --% is the mod function, gives you the remainder of the operation
--mod is essential when working with integers

Now consider:

select 1.0 / 1 = 1.000000

Numeric / Integer = Numeric

A trick to use to see the exact type is to use a sql_variant dataype:

declare @.value sql_variant
set @.value = 1.0 / 1

--give precision for integer even though not technically part of datatype
select cast(sql_variant_property(@.value,'baseType') as varchar(10))+
'(' + cast(sql_variant_property(@.value,'precision') as varchar(10))+ ',' +
cast (sql_variant_property(@.value,'scale') as varchar(10)) + ')'

select @.value

Using this query you can see what type is chosen for the output of an expression, either a scalar value, or a mathematic equation.

For the 1.0 / 1 before, this returns:

1.000000

SQL Server chooses a datatype that will always be able to store the result of the equation safely with no loss of precision if possible.

The point is that you have to be really careful with math because one little mess up on a type and your numbers are meaningless:

select (1/2) * 5000.00
select (1.0/2.0) * 5000

the first one is 0, because 1/2 returns integer 0 * numeric 5000.00 = 0.00

the second one 2500.000000, since 1.0/2.0 returns numeric(8,6) 0.500000 * 5000

Hopefully this is a bit clear. Datatype conversion can be tricky. In BOL (for 2005), look up Data Types [SQL Server]; Converting. There is an implicit conversion char to tell you what types will convert to what types as needed.

Problem using SP_TRACE_SETFILTER - it doesn't seem to apply the fi

Warning - this is quite a LONG post.
Hi all,
Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
want to do some tracing in our production environment, and have jobs set up
to automaGically run the traces, so I am building the traces in an SP, via
SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
SQL scheduled jobs, and will happily create the trace files. I have another
SP to then copy these files to another server, and automaGically import them
in some tables for analyzing.
All good and well, so far, EXCEPT that while my trace get's created with no
errors, when I look at the trace data, it seems that the filters are not
being applied. When I open it up from Profiler, I can see the trace
properties, and the Events and Data columns are all correct, but there are no
Filters. Anyone see where I am going wrong?
So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
The SP
if exists (select 1 from sysobjects where name = 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
required by SP_CREATE_TRACE. The File to be created. @.StopTime
datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt as
required by SP_CREATE_TRACE. The maximum size per
file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
On: 2004-12-28-- Purpose: -- This stored procedure was written to facilitate
the creation of profiler traces that write to files.-- -- Example:-- exec
Up_Auto_Profiler_Perf
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-- Possible
future additions:-- 1> simple enhancement to specify DB to store data, and
table.-- 2> accepting a parameter instead of apply the default .trc. Use the
.trc as default-- 3> implementing default value's for dir's, so that it can
run without parm's ... good or bad...- Change Control: version 1 - Regan
Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
generation of limited data, -- filtering on duration-- Added Output
parametre @.TracId to return for lookup on trace. --AS--Declare Control
Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
@.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
that when the max_file_size is reached, the current trace file is closed and
a new file is created.--Set Trace Filter to exclude System Ids - that is
ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
@.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On = 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
@.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
Cannot create tracefile - check if file already exists, or this trace already
running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
there is not enough memory to perform the specified action.' if @.ReturnCode = 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
Invalid parameters. Returned when the user supplied incompatible
parameters.' else Print 'Unexpected and Unknown error In creating trace -
Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
@.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
Cursor Recompileset @.EventId = 75exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this set
is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
@.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.EventId
= 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId = 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics--
set @.EventId = 98-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- set
@.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData--
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
@.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.EventId
= 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
Transaction-- set @.EventId = 50-- exec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
@.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
BatchCompletedset @.EventId = 12exec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- set
@.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- exec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
'ERROR 13 - Out of memory. Returned when there is not enough memory to
perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
Error' GoTo ErrorHandlerendexec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
currently running. Changing the trace at this time will result in an
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
= 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
@.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
enough memory to perform the specified action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
--Greater than exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
@.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
trace is currently running. Changing the trace at this time will result in an
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
The specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
@.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
Returned when there is not enough memory to perform the specified
action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
ErrorHandler endendErrorHandler:Return @.ReturnCode
The Code that calls the trace
DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
@.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
@.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
@.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
@.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
@.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize = 100 -- this is the maximum size for a file, in MBset @.DurationFilter = 1000--Set Running Variables--the time the trace will stopset @.RunStopTime = dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
UNC)set @.FileName = 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
char(3)),2) --Months +cast(datepart(d,getdate()) as
char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
char(3)),2) --Minutes--Display variablesprint 'File created is :
'+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
varchar(20))--Create trace, writing out to tracefile, until endtimeexec
@.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
Handlingselect @.rcselect @.TraceIDSELECT * FROM
::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
Ta...the data type of the @.durationfiltervalue must correspond with the data type
of the column to be filtered. otherwise it will not work.
unfortunately I was unable to post this into a QA session my connection kept
crashing .
Hope that helps
"Wanderer" wrote:
> Warning - this is quite a LONG post.
> Hi all,
> Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
> want to do some tracing in our production environment, and have jobs set up
> to automaGically run the traces, so I am building the traces in an SP, via
> SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
> SQL scheduled jobs, and will happily create the trace files. I have another
> SP to then copy these files to another server, and automaGically import them
> in some tables for analyzing.
> All good and well, so far, EXCEPT that while my trace get's created with no
> errors, when I look at the trace data, it seems that the filters are not
> being applied. When I open it up from Profiler, I can see the trace
> properties, and the Events and Data columns are all correct, but there are no
> Filters. Anyone see where I am going wrong?
> So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> The SP
> if exists (select 1 from sysobjects where name => 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
> required by SP_CREATE_TRACE. The File to be created. @.StopTime
> datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt as
> required by SP_CREATE_TRACE. The maximum size per
> file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> On: 2004-12-28-- Purpose: -- This stored procedure was written to facilitate
> the creation of profiler traces that write to files.-- -- Example:-- exec
> Up_Auto_Profiler_Perf
> 'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-- Possible
> future additions:-- 1> simple enhancement to specify DB to store data, and
> table.-- 2> accepting a parameter instead of apply the default .trc. Use the
> .trc as default-- 3> implementing default value's for dir's, so that it can
> run without parm's ... good or bad...- Change Control: version 1 - Regan
> Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> generation of limited data, -- filtering on duration-- Added Output
> parametre @.TracId to return for lookup on trace. --AS--Declare Control
> Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
> intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
> that when the max_file_size is reached, the current trace file is closed and
> a new file is created.--Set Trace Filter to exclude System Ids - that is
> ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
> than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
> output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
> @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
> options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
> Cannot create tracefile - check if file already exists, or this trace already
> running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
> there is not enough memory to perform the specified action.' if @.ReturnCode => 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> Invalid parameters. Returned when the user supplied incompatible
> parameters.' else Print 'Unexpected and Unknown error In creating trace -
> Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
> CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> @.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
> Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this set
> is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
> @.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.EventId
> = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
> Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId => 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics--
> set @.EventId = 98-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- set
> @.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData--
> exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.EventId
> = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
> Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
> BatchCompletedset @.EventId = 12exec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- set
> @.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- exec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
> The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
> 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
> Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
> 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
> currently running. Changing the trace at this time will result in an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
> valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
> allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
> 'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
> = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
> enough memory to perform the specified action.' else if @.ReturnCode => 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
> 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
> 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> --Greater than exec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
> @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
> trace is currently running. Changing the trace at this time will result in an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
> not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
> The specified Comparison Operator is not valid. ' else if @.ReturnCode => 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
> @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> Returned when there is not enough memory to perform the specified
> action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
> valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> ErrorHandler endendErrorHandler:Return @.ReturnCode
>
> The Code that calls the trace
> DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
> @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
> @.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize => 100 -- this is the maximum size for a file, in MBset @.DurationFilter => 1000--Set Running Variables--the time the trace will stopset @.RunStopTime => dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
> UNC)set @.FileName => 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> char(3)),2) --Months +cast(datepart(d,getdate()) as
> char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> char(3)),2) --Minutes--Display variablesprint 'File created is :
> '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> varchar(20))--Create trace, writing out to tracefile, until endtimeexec
> @.Rc=Up_Auto_Profiler_Perf
> @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> Handlingselect @.rcselect @.TraceIDSELECT * FROM
> ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
> Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
>
> Ta...
>|||Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before, and
got error message saying that it needed to be BIGINT.
"Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line 272
Procedure expects parameter '@.value' of type 'bigint'."
"Olu Adedeji" wrote:
> the data type of the @.durationfiltervalue must correspond with the data type
> of the column to be filtered. otherwise it will not work.
> unfortunately I was unable to post this into a QA session my connection kept
> crashing .
> Hope that helps
> "Wanderer" wrote:
> > Warning - this is quite a LONG post.
> >
> > Hi all,
> >
> > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
> > want to do some tracing in our production environment, and have jobs set up
> > to automaGically run the traces, so I am building the traces in an SP, via
> > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
> > SQL scheduled jobs, and will happily create the trace files. I have another
> > SP to then copy these files to another server, and automaGically import them
> > in some tables for analyzing.
> >
> > All good and well, so far, EXCEPT that while my trace get's created with no
> > errors, when I look at the trace data, it seems that the filters are not
> > being applied. When I open it up from Profiler, I can see the trace
> > properties, and the Events and Data columns are all correct, but there are no
> > Filters. Anyone see where I am going wrong?
> >
> > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > The SP
> >
> > if exists (select 1 from sysobjects where name => > 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> > procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
> > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt as
> > required by SP_CREATE_TRACE. The maximum size per
> > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > On: 2004-12-28-- Purpose: -- This stored procedure was written to facilitate
> > the creation of profiler traces that write to files.-- -- Example:-- exec
> > Up_Auto_Profiler_Perf
> > 'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-- Possible
> > future additions:-- 1> simple enhancement to specify DB to store data, and
> > table.-- 2> accepting a parameter instead of apply the default .trc. Use the
> > .trc as default-- 3> implementing default value's for dir's, so that it can
> > run without parm's ... good or bad...- Change Control: version 1 - Regan
> > Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> > generation of limited data, -- filtering on duration-- Added Output
> > parametre @.TracId to return for lookup on trace. --AS--Declare Control
> > Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
> > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> > int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
> > that when the max_file_size is reached, the current trace file is closed and
> > a new file is created.--Set Trace Filter to exclude System Ids - that is
> > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
> > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
> > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
> > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
> > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
> > Cannot create tracefile - check if file already exists, or this trace already
> > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
> > there is not enough memory to perform the specified action.' if @.ReturnCode => > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > Invalid parameters. Returned when the user supplied incompatible
> > parameters.' else Print 'Unexpected and Unknown error In creating trace -
> > Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
> > CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> > @.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
> > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this set
> > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
> > @.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.EventId
> > = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
> > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId => > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics--
> > set @.EventId = 98-- exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- set
> > @.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData--
> > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.EventId
> > = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
> > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
> > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > @.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- set
> > @.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- exec
> > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
> > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
> > The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
> > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
> > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
> > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
> > currently running. Changing the trace at this time will result in an
> > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
> > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
> > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
> > 'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
> > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
> > enough memory to perform the specified action.' else if @.ReturnCode => > 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
> > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
> > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > --Greater than exec sp_trace_setfilter
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
> > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
> > trace is currently running. Changing the trace at this time will result in an
> > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
> > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
> > The specified Comparison Operator is not valid. ' else if @.ReturnCode => > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
> > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > Returned when there is not enough memory to perform the specified
> > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
> > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > ErrorHandler endendErrorHandler:Return @.ReturnCode
> >
> >
> > The Code that calls the trace
> >
> > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
> > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
> > @.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize => > 100 -- this is the maximum size for a file, in MBset @.DurationFilter => > 1000--Set Running Variables--the time the trace will stopset @.RunStopTime => > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
> > UNC)set @.FileName => > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > varchar(20))--Create trace, writing out to tracefile, until endtimeexec
> > @.Rc=Up_Auto_Profiler_Perf
> > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
> > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> >
> >
> > Ta...
> >|||I have never had problems with this procedure, however I try and stick with
the syntax format as seen below(one of my earlier posts ie. using
@.bigintfilter and @.intfilter input parameters) because of the nature of the
procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
CREATE PROCEDURE sp_DBA_LRQ
@.duration int, -- in minutes
@.QueryDuration int = 5, -- in seconds
@.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
@.dbname sysname = null, -- the database to monitor (null = monitor all
databases)
@.maxfilesize bigint = 20, -- expected size of trc file in MB
@.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to include
backslash
AS
SET NOCOUNT ON
-- Name: sp_DBA_LRQ
-- Date: 22/10/2004
-- Author: Olu Adedeji
-- Note: auto set @.duration (mins)
-- auto set @.filename (no extension)
-- monitor @.dbname (database you wish to run the sql against)
-- The latest file will always be servername_yyyymmdd.trc
-- to use this locally set @.filedir = c:\temp\ -- remember to add the '\'
at the end of the directory
-- Capture Long Running Queries(LRQ)
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++
-- WHAT WHO WHEN
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++
-- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL) to
-- Resolve Execution plan annomalies on prod servers Olu
Adedeji 16/12/2004
-- Version: 1.04 - customised to capture specific information for PrecisDM
Olu Adedeji 16/12/2004
-- Version: 1.05 - customised to capture long running Queries Olu
Adedeji 22/12/2004
-- I have included to capture SQLBatches and Stored Procs
-- Display Procedure information
Print 'Application: SQL Profiler'
Print 'Procedure: sp_DBA_LRQ'
Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername + '
for ' + convert(varchar,@.duration) + '(mins)'
Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
-- clear down temp table
if (select object_id('tempdb.dbo.##fileexists') from
tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
drop table ##fileexists
-- Create a Queue
declare @.rc int,
@.TraceID int, -- sqlassigns the trace ID
@.DateTime datetime, -- trace stop time
@.CurrentTime datetime, -- current time
@.appendDate int, -- append datestamp
@.Cmd varchar(1000),
@.RenameCmd varchar(1000),
@.newFileName varchar(1000)
-- auto set stoptime
set @.CurrentTime = current_timestamp
set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
--set @.maxfilesize = 50 -- set max file size to 10Mb
set @.filename = @.filename + '_' + (select
convert(varchar,current_timestamp,112))
set @.newFileName = @.filename
set @.filename = @.filedir + @.filename
-- create temp table
create table ##fileexists (FileExists int, DirectoryExists int,
ParentDirectoryExists int)
-- New file name to rename to
declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
extension)
set @.ActualFilename = @.filename + '.trc'
-- populate temp table
insert into ##fileexists
exec master.dbo.xp_fileexist @.Actualfilename
-- Check that file exists and rename file or proceed if it does not exist
if (select fileexists from ##fileexists where fileexists = 1) is not null
begin
-- I have used 114 to allow for 24h time conversion (similar to how
logshipping works)
select @.appendDate = (select CASE WHEN
convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
convert(varchar,datepart(hh,current_timestamp),114)
WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
convert(varchar,datepart(hh,current_timestamp),114)
END
+ CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
convert(varchar,datepart(mi,current_timestamp),114)
END)
select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName + '_'
+ convert(varchar,@.AppendDate) + '.trc'
-- display the rename command (useful if file exists)
Print @.RenameCmd
exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
End
exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
@.Datetime
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
--++++++++++++++++++++++++++++++++++++++
-- Capture RPC:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 10, 1, @.on
exec sp_trace_setevent @.TraceID, 10, 2, @.on
exec sp_trace_setevent @.TraceID, 10, 3, @.on
exec sp_trace_setevent @.TraceID, 10, 8, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 21, @.on
exec sp_trace_setevent @.TraceID, 10, 26, @.on
exec sp_trace_setevent @.TraceID, 10, 35, @.on
--++++++++++++++++++++++++++++++++++++++
-- Capture SP:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 43, 1, @.on
exec sp_trace_setevent @.TraceID, 43, 2, @.on
exec sp_trace_setevent @.TraceID, 43, 3, @.on
exec sp_trace_setevent @.TraceID, 43, 8, @.on
exec sp_trace_setevent @.TraceID, 43, 9, @.on
exec sp_trace_setevent @.TraceID, 43, 10, @.on
exec sp_trace_setevent @.TraceID, 43, 11, @.on
exec sp_trace_setevent @.TraceID, 43, 12, @.on
exec sp_trace_setevent @.TraceID, 43, 13, @.on
exec sp_trace_setevent @.TraceID, 43, 14, @.on
exec sp_trace_setevent @.TraceID, 43, 15, @.on
exec sp_trace_setevent @.TraceID, 43, 21, @.on
exec sp_trace_setevent @.TraceID, 43, 26, @.on
exec sp_trace_setevent @.TraceID, 43, 35, @.on
--++++++++++++++++++++++++++++++++++++++
--Capture SQL:BatchCompleted
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 2, @.on
exec sp_trace_setevent @.TraceID, 12, 3, @.on
exec sp_trace_setevent @.TraceID, 12, 8, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 21, @.on
exec sp_trace_setevent @.TraceID, 12, 26, @.on
exec sp_trace_setevent @.TraceID, 12, 35, @.on
--++++++++++++++++++++++++++++++++++++++
-- Capture Generic Information
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 70, 1, @.on
exec sp_trace_setevent @.TraceID, 70, 2, @.on
exec sp_trace_setevent @.TraceID, 70, 3, @.on
exec sp_trace_setevent @.TraceID, 70, 8, @.on
exec sp_trace_setevent @.TraceID, 70, 9, @.on
exec sp_trace_setevent @.TraceID, 70, 10, @.on
exec sp_trace_setevent @.TraceID, 70, 11, @.on
exec sp_trace_setevent @.TraceID, 70, 12, @.on
exec sp_trace_setevent @.TraceID, 70, 14, @.on
exec sp_trace_setevent @.TraceID, 70, 21, @.on
exec sp_trace_setevent @.TraceID, 70, 26, @.on
exec sp_trace_setevent @.TraceID, 70, 35, @.on
exec sp_trace_setevent @.TraceID, 71, 1, @.on
exec sp_trace_setevent @.TraceID, 71, 2, @.on
exec sp_trace_setevent @.TraceID, 71, 3, @.on
exec sp_trace_setevent @.TraceID, 71, 8, @.on
exec sp_trace_setevent @.TraceID, 71, 9, @.on
exec sp_trace_setevent @.TraceID, 71, 10, @.on
exec sp_trace_setevent @.TraceID, 71, 11, @.on
exec sp_trace_setevent @.TraceID, 71, 12, @.on
exec sp_trace_setevent @.TraceID, 71, 14, @.on
exec sp_trace_setevent @.TraceID, 71, 21, @.on
exec sp_trace_setevent @.TraceID, 71, 26, @.on
exec sp_trace_setevent @.TraceID, 71, 35, @.on
exec sp_trace_setevent @.TraceID, 72, 1, @.on
exec sp_trace_setevent @.TraceID, 72, 2, @.on
exec sp_trace_setevent @.TraceID, 72, 3, @.on
exec sp_trace_setevent @.TraceID, 72, 8, @.on
exec sp_trace_setevent @.TraceID, 72, 9, @.on
exec sp_trace_setevent @.TraceID, 72, 10, @.on
exec sp_trace_setevent @.TraceID, 72, 11, @.on
exec sp_trace_setevent @.TraceID, 72, 12, @.on
exec sp_trace_setevent @.TraceID, 72, 14, @.on
exec sp_trace_setevent @.TraceID, 72, 21, @.on
exec sp_trace_setevent @.TraceID, 72, 26, @.on
exec sp_trace_setevent @.TraceID, 72, 35, @.on
exec sp_trace_setevent @.TraceID, 74, 1, @.on
exec sp_trace_setevent @.TraceID, 74, 2, @.on
exec sp_trace_setevent @.TraceID, 74, 3, @.on
exec sp_trace_setevent @.TraceID, 74, 8, @.on
exec sp_trace_setevent @.TraceID, 74, 9, @.on
exec sp_trace_setevent @.TraceID, 74, 10, @.on
exec sp_trace_setevent @.TraceID, 74, 11, @.on
exec sp_trace_setevent @.TraceID, 74, 12, @.on
exec sp_trace_setevent @.TraceID, 74, 14, @.on
exec sp_trace_setevent @.TraceID, 74, 21, @.on
exec sp_trace_setevent @.TraceID, 74, 26, @.on
exec sp_trace_setevent @.TraceID, 74, 35, @.on
exec sp_trace_setevent @.TraceID, 77, 1, @.on
exec sp_trace_setevent @.TraceID, 77, 2, @.on
exec sp_trace_setevent @.TraceID, 77, 3, @.on
exec sp_trace_setevent @.TraceID, 77, 8, @.on
exec sp_trace_setevent @.TraceID, 77, 9, @.on
exec sp_trace_setevent @.TraceID, 77, 10, @.on
exec sp_trace_setevent @.TraceID, 77, 11, @.on
exec sp_trace_setevent @.TraceID, 77, 12, @.on
exec sp_trace_setevent @.TraceID, 77, 14, @.on
exec sp_trace_setevent @.TraceID, 77, 21, @.on
exec sp_trace_setevent @.TraceID, 77, 26, @.on
exec sp_trace_setevent @.TraceID, 77, 35, @.on
exec sp_trace_setevent @.TraceID, 78, 1, @.on
exec sp_trace_setevent @.TraceID, 78, 2, @.on
exec sp_trace_setevent @.TraceID, 78, 3, @.on
exec sp_trace_setevent @.TraceID, 78, 8, @.on
exec sp_trace_setevent @.TraceID, 78, 9, @.on
exec sp_trace_setevent @.TraceID, 78, 10, @.on
exec sp_trace_setevent @.TraceID, 78, 11, @.on
exec sp_trace_setevent @.TraceID, 78, 12, @.on
exec sp_trace_setevent @.TraceID, 78, 14, @.on
exec sp_trace_setevent @.TraceID, 78, 21, @.on
exec sp_trace_setevent @.TraceID, 78, 26, @.on
exec sp_trace_setevent @.TraceID, 78, 35, @.on
exec sp_trace_setevent @.TraceID, 100, 1, @.on
exec sp_trace_setevent @.TraceID, 100, 2, @.on
exec sp_trace_setevent @.TraceID, 100, 3, @.on
exec sp_trace_setevent @.TraceID, 100, 8, @.on
exec sp_trace_setevent @.TraceID, 100, 9, @.on
exec sp_trace_setevent @.TraceID, 100, 10, @.on
exec sp_trace_setevent @.TraceID, 100, 11, @.on
exec sp_trace_setevent @.TraceID, 100, 12, @.on
exec sp_trace_setevent @.TraceID, 100, 14, @.on
exec sp_trace_setevent @.TraceID, 100, 21, @.on
exec sp_trace_setevent @.TraceID, 100, 26, @.on
exec sp_trace_setevent @.TraceID, 100, 35, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
-- set database filter if @.dbname is not null
if @.dbname is not null
begin
-- check @.dbname is on the server
if (select db_id(@.dbname)) is not null
set @.intfilter = db_id(@.dbname)
exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
end
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- set filter for Long Running Query(LRQ) Duration
-- Added this 22/12/2004
select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
milliseconds(ms)
exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
-- 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:
-- display Trace information (can output this to a logfile in sqlagent)
select * from ::fn_trace_getinfo(default)
-- clear down temp table
if (select object_id('tempdb.dbo.##fileexists') from
tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
drop table ##fileexists
GO
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before,
and
> got error message saying that it needed to be BIGINT.
> "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line
272
> Procedure expects parameter '@.value' of type 'bigint'."
> "Olu Adedeji" wrote:
> > the data type of the @.durationfiltervalue must correspond with the data
type
> > of the column to be filtered. otherwise it will not work.
> >
> > unfortunately I was unable to post this into a QA session my connection
kept
> > crashing .
> >
> > Hope that helps
> >
> > "Wanderer" wrote:
> >
> > > Warning - this is quite a LONG post.
> > >
> > > Hi all,
> > >
> > > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the
picture - I
> > > want to do some tracing in our production environment, and have jobs
set up
> > > to automaGically run the traces, so I am building the traces in an SP,
via
> > > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get
called from
> > > SQL scheduled jobs, and will happily create the trace files. I have
another
> > > SP to then copy these files to another server, and automaGically
import them
> > > in some tables for analyzing.
> > >
> > > All good and well, so far, EXCEPT that while my trace get's created
with no
> > > errors, when I look at the trace data, it seems that the filters are
not
> > > being applied. When I open it up from Profiler, I can see the trace
> > > properties, and the Events and Data columns are all correct, but there
are no
> > > Filters. Anyone see where I am going wrong?
> > >
> > > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > > The SP
> > >
> > > if exists (select 1 from sysobjects where name => > > 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> > > procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar
as
> > > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > > datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt
as
> > > required by SP_CREATE_TRACE. The maximum size per
> > > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> > > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > > On: 2004-12-28-- Purpose: -- This stored procedure was written to
facilitate
> > > the creation of profiler traces that write to files.-- -- Example:--
exec
> > > Up_Auto_Profiler_Perf
> > >
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-
-- Possible
> > > future additions:-- 1> simple enhancement to specify DB to store data,
and
> > > table.-- 2> accepting a parameter instead of apply the default .trc.
Use the
> > > .trc as default-- 3> implementing default value's for dir's, so that
it can
> > > run without parm's ... good or bad...- Change Control: version 1 -
Regan
> > > Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> > > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> > > generation of limited data, -- filtering on duration-- Added Output
> > > parametre @.TracId to return for lookup on trace. --AS--Declare
Control
> > > Variabledeclare @.ReturnCode Int--Declare Option Variables declare
@.Option
> > > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > > @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> > > int--Set Option Variableset @.Option =2 --TraceFileRollOver --Specifies
> > > that when the max_file_size is reached, the current trace file is
closed and
> > > a new file is created.--Set Trace Filter to exclude System Ids - that
is
> > > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator =2 --Greater
> > > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create
@.TraceId
> > > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect
@.ReturnCode=@.@.Errorif
> > > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
Returned when
> > > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error
12 -
> > > Cannot create tracefile - check if file already exists, or this trace
already
> > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned
when
> > > there is not enough memory to perform the specified action.' if
@.ReturnCode => > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > > Invalid parameters. Returned when the user supplied incompatible
> > > parameters.' else Print 'Unexpected and Unknown error In creating
trace -
> > > Please review' Goto ErrorHandlerEnd--Populate Trace with
Events--SECTION
> > > CURSORS-- this set is : cursor executeset @.EventId = 74exec
sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> > > @.EventId = 53exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec
> > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is
:
> > > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS--
this set
> > > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> > > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
Lock:DeadLockChainset
> > > @.EventId = 59exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent
> > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset
@.EventId
> > > = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is
:
> > > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset
@.EventId => > > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan
Statistics--
> > > set @.EventId = 98-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan
Text-- set
> > > @.EventId = 96-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData--
> > > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> > > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec
sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset
@.EventId
> > > = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
sp_trace_setevent
> > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is :
SQL
> > > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is :
SQL
> > > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,17,@.On --Writes-- --this set is :
SQL:StmtCompleted-- set
> > > @.EventId = 41-- exec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpu-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec
sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus
@.TraceId,1select
> > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> > > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR
9 -
> > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
print
> > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 -
Unknown
> > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
'ERROR
> > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace
is
> > > currently running. Changing the trace at this time will result in an
> > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
is not
> > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
is not
> > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7
print
> > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
@.ReturnCode
> > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there
is not
> > > enough memory to perform the specified action.' else if @.ReturnCode => > > 16 print 'ERROR 16 - The function is not valid for this trace.' else
Print
> > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue
<>
> > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > --Greater than exec sp_trace_setfilter
> > >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
select
> > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 -
The
> > > trace is currently running. Changing the trace at this time will
result in an
> > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
is
> > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
Column
> > > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR
6 -
> > > The specified Comparison Operator is not valid. ' else if @.ReturnCode
=> > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else
if
> > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > Returned when there is not enough memory to perform the specified
> > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is
not
> > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > >
> > >
> > > The Code that calls the trace
> > >
> > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
INTEGERDECLARE
> > > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> > > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control
Variablesset
> > > @.MinutesToRun = 5 -- this is how long the trace will run forset
@.MaxSize => > > 100 -- this is the maximum size for a file, in MBset @.DurationFilter => > > 1000--Set Running Variables--the time the trace will stopset
@.RunStopTime => > > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name
(not
> > > UNC)set @.FileName => > > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > > varchar(20))--Create trace, writing out to tracefile, until
endtimeexec
> > > @.Rc=Up_Auto_Profiler_Perf
> > > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> > > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR
('Create/Run of
> > > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> > >
> > >
> > > Ta...
> > >|||I'm not having datatype errors. The SP runs fine, EXCEPT that it doesn't
seem to apply the filter. When I open the trace file in Profiler, there are
NO filters set. Yet the SP_TRACE_SETFILTER does not return any error's
(you'll see I do do error checking after the SP_TRACE_SETFILTER commands.
"Olu Adedeji" wrote:
> I have never had problems with this procedure, however I try and stick with
> the syntax format as seen below(one of my earlier posts ie. using
> @.bigintfilter and @.intfilter input parameters) because of the nature of the
> procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
>
> CREATE PROCEDURE sp_DBA_LRQ
> @.duration int, -- in minutes
> @.QueryDuration int = 5, -- in seconds
> @.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
> @.dbname sysname = null, -- the database to monitor (null = monitor all
> databases)
> @.maxfilesize bigint = 20, -- expected size of trc file in MB
> @.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to include
> backslash
> AS
> SET NOCOUNT ON
> -- Name: sp_DBA_LRQ
> -- Date: 22/10/2004
> -- Author: Olu Adedeji
> -- Note: auto set @.duration (mins)
> -- auto set @.filename (no extension)
> -- monitor @.dbname (database you wish to run the sql against)
> -- The latest file will always be servername_yyyymmdd.trc
> -- to use this locally set @.filedir = c:\temp\ -- remember to add the '\'
> at the end of the directory
> -- Capture Long Running Queries(LRQ)
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++++++++++++++++++++++++++++++++
> -- WHAT WHO WHEN
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++++
> -- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL) to
> -- Resolve Execution plan annomalies on prod servers Olu
> Adedeji 16/12/2004
> -- Version: 1.04 - customised to capture specific information for PrecisDM
> Olu Adedeji 16/12/2004
> -- Version: 1.05 - customised to capture long running Queries Olu
> Adedeji 22/12/2004
> -- I have included to capture SQLBatches and Stored Procs
> -- Display Procedure information
> Print 'Application: SQL Profiler'
> Print 'Procedure: sp_DBA_LRQ'
> Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername + '
> for ' + convert(varchar,@.duration) + '(mins)'
> Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
> -- clear down temp table
> if (select object_id('tempdb.dbo.##fileexists') from
> tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> drop table ##fileexists
> -- Create a Queue
> declare @.rc int,
> @.TraceID int, -- sqlassigns the trace ID
> @.DateTime datetime, -- trace stop time
> @.CurrentTime datetime, -- current time
> @.appendDate int, -- append datestamp
> @.Cmd varchar(1000),
> @.RenameCmd varchar(1000),
> @.newFileName varchar(1000)
> -- auto set stoptime
> set @.CurrentTime = current_timestamp
> set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
> --set @.maxfilesize = 50 -- set max file size to 10Mb
> set @.filename = @.filename + '_' + (select
> convert(varchar,current_timestamp,112))
> set @.newFileName = @.filename
> set @.filename = @.filedir + @.filename
> -- create temp table
> create table ##fileexists (FileExists int, DirectoryExists int,
> ParentDirectoryExists int)
> -- New file name to rename to
> declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
> extension)
> set @.ActualFilename = @.filename + '.trc'
> -- populate temp table
> insert into ##fileexists
> exec master.dbo.xp_fileexist @.Actualfilename
>
> -- Check that file exists and rename file or proceed if it does not exist
> if (select fileexists from ##fileexists where fileexists = 1) is not null
> begin
> -- I have used 114 to allow for 24h time conversion (similar to how
> logshipping works)
> select @.appendDate = (select CASE WHEN
> convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
> convert(varchar,datepart(hh,current_timestamp),114)
> WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
> convert(varchar,datepart(hh,current_timestamp),114)
> END
> + CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
> THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
> WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
> convert(varchar,datepart(mi,current_timestamp),114)
> END)
> select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName + '_'
> + convert(varchar,@.AppendDate) + '.trc'
> -- display the rename command (useful if file exists)
> Print @.RenameCmd
> exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
> End
> exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
> @.Datetime
> if (@.rc != 0) goto error
> -- Client side File and Table cannot be scripted
> -- Set the events
> declare @.on bit
> set @.on = 1
> --++++++++++++++++++++++++++++++++++++++
> -- Capture RPC:Completed
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 10, 1, @.on
> exec sp_trace_setevent @.TraceID, 10, 2, @.on
> exec sp_trace_setevent @.TraceID, 10, 3, @.on
> exec sp_trace_setevent @.TraceID, 10, 8, @.on
> exec sp_trace_setevent @.TraceID, 10, 9, @.on
> exec sp_trace_setevent @.TraceID, 10, 10, @.on
> exec sp_trace_setevent @.TraceID, 10, 11, @.on
> exec sp_trace_setevent @.TraceID, 10, 12, @.on
> exec sp_trace_setevent @.TraceID, 10, 13, @.on
> exec sp_trace_setevent @.TraceID, 10, 14, @.on
> exec sp_trace_setevent @.TraceID, 10, 15, @.on
> exec sp_trace_setevent @.TraceID, 10, 21, @.on
> exec sp_trace_setevent @.TraceID, 10, 26, @.on
> exec sp_trace_setevent @.TraceID, 10, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> -- Capture SP:Completed
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 43, 1, @.on
> exec sp_trace_setevent @.TraceID, 43, 2, @.on
> exec sp_trace_setevent @.TraceID, 43, 3, @.on
> exec sp_trace_setevent @.TraceID, 43, 8, @.on
> exec sp_trace_setevent @.TraceID, 43, 9, @.on
> exec sp_trace_setevent @.TraceID, 43, 10, @.on
> exec sp_trace_setevent @.TraceID, 43, 11, @.on
> exec sp_trace_setevent @.TraceID, 43, 12, @.on
> exec sp_trace_setevent @.TraceID, 43, 13, @.on
> exec sp_trace_setevent @.TraceID, 43, 14, @.on
> exec sp_trace_setevent @.TraceID, 43, 15, @.on
> exec sp_trace_setevent @.TraceID, 43, 21, @.on
> exec sp_trace_setevent @.TraceID, 43, 26, @.on
> exec sp_trace_setevent @.TraceID, 43, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> --Capture SQL:BatchCompleted
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 12, 1, @.on
> exec sp_trace_setevent @.TraceID, 12, 2, @.on
> exec sp_trace_setevent @.TraceID, 12, 3, @.on
> exec sp_trace_setevent @.TraceID, 12, 8, @.on
> exec sp_trace_setevent @.TraceID, 12, 9, @.on
> exec sp_trace_setevent @.TraceID, 12, 10, @.on
> exec sp_trace_setevent @.TraceID, 12, 11, @.on
> exec sp_trace_setevent @.TraceID, 12, 12, @.on
> exec sp_trace_setevent @.TraceID, 12, 13, @.on
> exec sp_trace_setevent @.TraceID, 12, 14, @.on
> exec sp_trace_setevent @.TraceID, 12, 15, @.on
> exec sp_trace_setevent @.TraceID, 12, 21, @.on
> exec sp_trace_setevent @.TraceID, 12, 26, @.on
> exec sp_trace_setevent @.TraceID, 12, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> -- Capture Generic Information
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 70, 1, @.on
> exec sp_trace_setevent @.TraceID, 70, 2, @.on
> exec sp_trace_setevent @.TraceID, 70, 3, @.on
> exec sp_trace_setevent @.TraceID, 70, 8, @.on
> exec sp_trace_setevent @.TraceID, 70, 9, @.on
> exec sp_trace_setevent @.TraceID, 70, 10, @.on
> exec sp_trace_setevent @.TraceID, 70, 11, @.on
> exec sp_trace_setevent @.TraceID, 70, 12, @.on
> exec sp_trace_setevent @.TraceID, 70, 14, @.on
> exec sp_trace_setevent @.TraceID, 70, 21, @.on
> exec sp_trace_setevent @.TraceID, 70, 26, @.on
> exec sp_trace_setevent @.TraceID, 70, 35, @.on
> exec sp_trace_setevent @.TraceID, 71, 1, @.on
> exec sp_trace_setevent @.TraceID, 71, 2, @.on
> exec sp_trace_setevent @.TraceID, 71, 3, @.on
> exec sp_trace_setevent @.TraceID, 71, 8, @.on
> exec sp_trace_setevent @.TraceID, 71, 9, @.on
> exec sp_trace_setevent @.TraceID, 71, 10, @.on
> exec sp_trace_setevent @.TraceID, 71, 11, @.on
> exec sp_trace_setevent @.TraceID, 71, 12, @.on
> exec sp_trace_setevent @.TraceID, 71, 14, @.on
> exec sp_trace_setevent @.TraceID, 71, 21, @.on
> exec sp_trace_setevent @.TraceID, 71, 26, @.on
> exec sp_trace_setevent @.TraceID, 71, 35, @.on
> exec sp_trace_setevent @.TraceID, 72, 1, @.on
> exec sp_trace_setevent @.TraceID, 72, 2, @.on
> exec sp_trace_setevent @.TraceID, 72, 3, @.on
> exec sp_trace_setevent @.TraceID, 72, 8, @.on
> exec sp_trace_setevent @.TraceID, 72, 9, @.on
> exec sp_trace_setevent @.TraceID, 72, 10, @.on
> exec sp_trace_setevent @.TraceID, 72, 11, @.on
> exec sp_trace_setevent @.TraceID, 72, 12, @.on
> exec sp_trace_setevent @.TraceID, 72, 14, @.on
> exec sp_trace_setevent @.TraceID, 72, 21, @.on
> exec sp_trace_setevent @.TraceID, 72, 26, @.on
> exec sp_trace_setevent @.TraceID, 72, 35, @.on
> exec sp_trace_setevent @.TraceID, 74, 1, @.on
> exec sp_trace_setevent @.TraceID, 74, 2, @.on
> exec sp_trace_setevent @.TraceID, 74, 3, @.on
> exec sp_trace_setevent @.TraceID, 74, 8, @.on
> exec sp_trace_setevent @.TraceID, 74, 9, @.on
> exec sp_trace_setevent @.TraceID, 74, 10, @.on
> exec sp_trace_setevent @.TraceID, 74, 11, @.on
> exec sp_trace_setevent @.TraceID, 74, 12, @.on
> exec sp_trace_setevent @.TraceID, 74, 14, @.on
> exec sp_trace_setevent @.TraceID, 74, 21, @.on
> exec sp_trace_setevent @.TraceID, 74, 26, @.on
> exec sp_trace_setevent @.TraceID, 74, 35, @.on
> exec sp_trace_setevent @.TraceID, 77, 1, @.on
> exec sp_trace_setevent @.TraceID, 77, 2, @.on
> exec sp_trace_setevent @.TraceID, 77, 3, @.on
> exec sp_trace_setevent @.TraceID, 77, 8, @.on
> exec sp_trace_setevent @.TraceID, 77, 9, @.on
> exec sp_trace_setevent @.TraceID, 77, 10, @.on
> exec sp_trace_setevent @.TraceID, 77, 11, @.on
> exec sp_trace_setevent @.TraceID, 77, 12, @.on
> exec sp_trace_setevent @.TraceID, 77, 14, @.on
> exec sp_trace_setevent @.TraceID, 77, 21, @.on
> exec sp_trace_setevent @.TraceID, 77, 26, @.on
> exec sp_trace_setevent @.TraceID, 77, 35, @.on
> exec sp_trace_setevent @.TraceID, 78, 1, @.on
> exec sp_trace_setevent @.TraceID, 78, 2, @.on
> exec sp_trace_setevent @.TraceID, 78, 3, @.on
> exec sp_trace_setevent @.TraceID, 78, 8, @.on
> exec sp_trace_setevent @.TraceID, 78, 9, @.on
> exec sp_trace_setevent @.TraceID, 78, 10, @.on
> exec sp_trace_setevent @.TraceID, 78, 11, @.on
> exec sp_trace_setevent @.TraceID, 78, 12, @.on
> exec sp_trace_setevent @.TraceID, 78, 14, @.on
> exec sp_trace_setevent @.TraceID, 78, 21, @.on
> exec sp_trace_setevent @.TraceID, 78, 26, @.on
> exec sp_trace_setevent @.TraceID, 78, 35, @.on
> exec sp_trace_setevent @.TraceID, 100, 1, @.on
> exec sp_trace_setevent @.TraceID, 100, 2, @.on
> exec sp_trace_setevent @.TraceID, 100, 3, @.on
> exec sp_trace_setevent @.TraceID, 100, 8, @.on
> exec sp_trace_setevent @.TraceID, 100, 9, @.on
> exec sp_trace_setevent @.TraceID, 100, 10, @.on
> exec sp_trace_setevent @.TraceID, 100, 11, @.on
> exec sp_trace_setevent @.TraceID, 100, 12, @.on
> exec sp_trace_setevent @.TraceID, 100, 14, @.on
> exec sp_trace_setevent @.TraceID, 100, 21, @.on
> exec sp_trace_setevent @.TraceID, 100, 26, @.on
> exec sp_trace_setevent @.TraceID, 100, 35, @.on
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> -- set database filter if @.dbname is not null
> if @.dbname is not null
> begin
> -- check @.dbname is on the server
> if (select db_id(@.dbname)) is not null
> set @.intfilter = db_id(@.dbname)
> exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> end
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> -- set filter for Long Running Query(LRQ) Duration
> -- Added this 22/12/2004
> select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
> milliseconds(ms)
> exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
>
> -- 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:
> -- display Trace information (can output this to a logfile in sqlagent)
> select * from ::fn_trace_getinfo(default)
> -- clear down temp table
> if (select object_id('tempdb.dbo.##fileexists') from
> tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> drop table ##fileexists
> GO
> "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> > Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before,
> and
> > got error message saying that it needed to be BIGINT.
> >
> > "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line
> 272
> > Procedure expects parameter '@.value' of type 'bigint'."
> >
> > "Olu Adedeji" wrote:
> >
> > > the data type of the @.durationfiltervalue must correspond with the data
> type
> > > of the column to be filtered. otherwise it will not work.
> > >
> > > unfortunately I was unable to post this into a QA session my connection
> kept
> > > crashing .
> > >
> > > Hope that helps
> > >
> > > "Wanderer" wrote:
> > >
> > > > Warning - this is quite a LONG post.
> > > >
> > > > Hi all,
> > > >
> > > > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the
> picture - I
> > > > want to do some tracing in our production environment, and have jobs
> set up
> > > > to automaGically run the traces, so I am building the traces in an SP,
> via
> > > > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get
> called from
> > > > SQL scheduled jobs, and will happily create the trace files. I have
> another
> > > > SP to then copy these files to another server, and automaGically
> import them
> > > > in some tables for analyzing.
> > > >
> > > > All good and well, so far, EXCEPT that while my trace get's created
> with no
> > > > errors, when I look at the trace data, it seems that the filters are
> not
> > > > being applied. When I open it up from Profiler, I can see the trace
> > > > properties, and the Events and Data columns are all correct, but there
> are no
> > > > Filters. Anyone see where I am going wrong?
> > > >
> > > > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > > > The SP
> > > >
> > > > if exists (select 1 from sysobjects where name => > > > 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> > > > procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar
> as
> > > > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > > > datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt
> as
> > > > required by SP_CREATE_TRACE. The maximum size per
> > > > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> > > > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > > > On: 2004-12-28-- Purpose: -- This stored procedure was written to
> facilitate
> > > > the creation of profiler traces that write to files.-- -- Example:--
> exec
> > > > Up_Auto_Profiler_Perf
> > > >
> 'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-
> -- Possible
> > > > future additions:-- 1> simple enhancement to specify DB to store data,
> and
> > > > table.-- 2> accepting a parameter instead of apply the default .trc.
> Use the
> > > > .trc as default-- 3> implementing default value's for dir's, so that
> it can
> > > > run without parm's ... good or bad...- Change Control: version 1 -
> Regan
> > > > Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> > > > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> > > > generation of limited data, -- filtering on duration-- Added Output
> > > > parametre @.TracId to return for lookup on trace. --AS--Declare
> Control
> > > > Variabledeclare @.ReturnCode Int--Declare Option Variables declare
> @.Option
> > > > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > > > @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> > > > int--Set Option Variableset @.Option => 2 --TraceFileRollOver --Specifies
> > > > that when the max_file_size is reached, the current trace file is
> closed and
> > > > a new file is created.--Set Trace Filter to exclude System Ids - that
> is
> > > > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > > > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator => 2 --Greater
> > > > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > > > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create
> @.TraceId
> > > > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect
> @.ReturnCode=@.@.Errorif
> > > > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
> Returned when
> > > > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error
> 12 -
> > > > Cannot create tracefile - check if file already exists, or this trace
> already
> > > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned
> when
> > > > there is not enough memory to perform the specified action.' if
> @.ReturnCode => > > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > > > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > > > Invalid parameters. Returned when the user supplied incompatible
> > > > parameters.' else Print 'Unexpected and Unknown error In creating
> trace -
> > > > Please review' Goto ErrorHandlerEnd--Populate Trace with
> Events--SECTION
> > > > CURSORS-- this set is : cursor executeset @.EventId = 74exec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> > > > @.EventId = 53exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec
> > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is
> :
> > > > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS--
> this set
> > > > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> > > > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > > > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
> Lock:DeadLockChainset
> > > > @.EventId = 59exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset
> @.EventId
> > > > = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is
> :
> > > > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset
> @.EventId => > > > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan
> Statistics--
> > > > set @.EventId = 98-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan
> Text-- set
> > > > @.EventId = 96-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData--
> > > > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> > > > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset
> @.EventId
> > > > = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is :
> SQL
> > > > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is :
> SQL
> > > > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,17,@.On --Writes-- --this set is :
> SQL:StmtCompleted-- set
> > > > @.EventId = 41-- exec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On --cpu-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus
> @.TraceId,1select
> > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> > > > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > > > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR
> 9 -
> > > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
> print
> > > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 -
> Unknown
> > > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> 'ERROR
> > > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace
> is
> > > > currently running. Changing the trace at this time will result in an
> > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
> is not
> > > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> is not
> > > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > > > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7
> print
> > > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
> @.ReturnCode
> > > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there
> is not
> > > > enough memory to perform the specified action.' else if @.ReturnCode => > > > 16 print 'ERROR 16 - The function is not valid for this trace.' else
> Print
> > > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue
> <>
> > > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > > --Greater than exec sp_trace_setfilter
> > > >
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> select
> > > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 -
> The
> > > > trace is currently running. Changing the trace at this time will
> result in an
> > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
> is
> > > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
> Column
> > > > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR
> 6 -
> > > > The specified Comparison Operator is not valid. ' else if @.ReturnCode
> => > > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else
> if
> > > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > > Returned when there is not enough memory to perform the specified
> > > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is
> not
> > > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > > >
> > > >
> > > > The Code that calls the trace
> > > >
> > > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
> INTEGERDECLARE
> > > > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> > > > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control
> Variablesset
> > > > @.MinutesToRun = 5 -- this is how long the trace will run forset
> @.MaxSize => > > > 100 -- this is the maximum size for a file, in MBset @.DurationFilter => > > > 1000--Set Running Variables--the time the trace will stopset
> @.RunStopTime => > > > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name
> (not
> > > > UNC)set @.FileName => > > > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > > > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > > > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > > > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > > > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > > > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > > > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > > > varchar(20))--Create trace, writing out to tracefile, until
> endtimeexec
> > > > @.Rc=Up_Auto_Profiler_Perf
> > > > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> > > > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > > > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR
> ('Create/Run of
> > > > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> > > >
> > > >
> > > > Ta...
> > > >
>
>|||Wanderer,
as per my email, I was intimating that you open profiler and set the filter
manually, then script out the trace and cut the section that performs the
sp_trace_setfilter into your procedure substituting your parameters for
hardcoded values. then run your procedure and check the trc files to see if
the traces have been set. . from your post it is obvious that profiler does
not understand the parameters you have passed into sp_trace_setfilter and as
a result it is unable to present it in the trc file. PS. out of interest
what are you trying to filter?
PS. I don't dispute that your trace runs fine . . I am merely trying to
help resolve the problem
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:B40C9908-9984-452E-88A6-FF3D1A453ABD@.microsoft.com...
> I'm not having datatype errors. The SP runs fine, EXCEPT that it doesn't
> seem to apply the filter. When I open the trace file in Profiler, there
are
> NO filters set. Yet the SP_TRACE_SETFILTER does not return any error's
> (you'll see I do do error checking after the SP_TRACE_SETFILTER commands.
>
> "Olu Adedeji" wrote:
> > I have never had problems with this procedure, however I try and stick
with
> > the syntax format as seen below(one of my earlier posts ie. using
> > @.bigintfilter and @.intfilter input parameters) because of the nature of
the
> > procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
> >
> >
> > CREATE PROCEDURE sp_DBA_LRQ
> > @.duration int, -- in minutes
> > @.QueryDuration int = 5, -- in seconds
> > @.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
> > @.dbname sysname = null, -- the database to monitor (null = monitor all
> > databases)
> > @.maxfilesize bigint = 20, -- expected size of trc file in MB
> > @.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to
include
> > backslash
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > -- Name: sp_DBA_LRQ
> > -- Date: 22/10/2004
> > -- Author: Olu Adedeji
> > -- Note: auto set @.duration (mins)
> > -- auto set @.filename (no extension)
> > -- monitor @.dbname (database you wish to run the sql against)
> > -- The latest file will always be servername_yyyymmdd.trc
> > -- to use this locally set @.filedir = c:\temp\ -- remember to add the
'\'
> > at the end of the directory
> > -- Capture Long Running Queries(LRQ)
> >
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++
> > ++++++++++++++++++++++++++++++++
> > -- WHAT WHO WHEN
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++
> > +++++++++++++++++++++++++++++++++
> > -- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL)
to
> > -- Resolve Execution plan annomalies on prod servers Olu
> > Adedeji 16/12/2004
> > -- Version: 1.04 - customised to capture specific information for
PrecisDM
> > Olu Adedeji 16/12/2004
> > -- Version: 1.05 - customised to capture long running Queries Olu
> > Adedeji 22/12/2004
> > -- I have included to capture SQLBatches and Stored Procs
> >
> > -- Display Procedure information
> >
> > Print 'Application: SQL Profiler'
> > Print 'Procedure: sp_DBA_LRQ'
> > Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername +
'
> > for ' + convert(varchar,@.duration) + '(mins)'
> > Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
> >
> > -- clear down temp table
> > if (select object_id('tempdb.dbo.##fileexists') from
> > tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> > drop table ##fileexists
> >
> > -- Create a Queue
> > declare @.rc int,
> > @.TraceID int, -- sqlassigns the trace ID
> > @.DateTime datetime, -- trace stop time
> > @.CurrentTime datetime, -- current time
> > @.appendDate int, -- append datestamp
> > @.Cmd varchar(1000),
> > @.RenameCmd varchar(1000),
> > @.newFileName varchar(1000)
> >
> > -- auto set stoptime
> > set @.CurrentTime = current_timestamp
> > set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
> > --set @.maxfilesize = 50 -- set max file size to 10Mb
> > set @.filename = @.filename + '_' + (select
> > convert(varchar,current_timestamp,112))
> > set @.newFileName = @.filename
> > set @.filename = @.filedir + @.filename
> >
> > -- create temp table
> > create table ##fileexists (FileExists int, DirectoryExists int,
> > ParentDirectoryExists int)
> >
> > -- New file name to rename to
> > declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
> > extension)
> > set @.ActualFilename = @.filename + '.trc'
> >
> > -- populate temp table
> > insert into ##fileexists
> > exec master.dbo.xp_fileexist @.Actualfilename
> >
> >
> > -- Check that file exists and rename file or proceed if it does not
exist
> > if (select fileexists from ##fileexists where fileexists = 1) is not
null
> > begin
> > -- I have used 114 to allow for 24h time conversion (similar to how
> > logshipping works)
> > select @.appendDate = (select CASE WHEN
> > convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
> > convert(varchar,datepart(hh,current_timestamp),114)
> > WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
> > convert(varchar,datepart(hh,current_timestamp),114)
> > END
> > + CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
> > THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
> > WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
> > convert(varchar,datepart(mi,current_timestamp),114)
> > END)
> > select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName +
'_'
> > + convert(varchar,@.AppendDate) + '.trc'
> >
> > -- display the rename command (useful if file exists)
> > Print @.RenameCmd
> > exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
> > End
> >
> > exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
> > @.Datetime
> > if (@.rc != 0) goto error
> >
> > -- Client side File and Table cannot be scripted
> >
> > -- Set the events
> > declare @.on bit
> > set @.on = 1
> >
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture RPC:Completed
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 10, 1, @.on
> > exec sp_trace_setevent @.TraceID, 10, 2, @.on
> > exec sp_trace_setevent @.TraceID, 10, 3, @.on
> > exec sp_trace_setevent @.TraceID, 10, 8, @.on
> > exec sp_trace_setevent @.TraceID, 10, 9, @.on
> > exec sp_trace_setevent @.TraceID, 10, 10, @.on
> > exec sp_trace_setevent @.TraceID, 10, 11, @.on
> > exec sp_trace_setevent @.TraceID, 10, 12, @.on
> > exec sp_trace_setevent @.TraceID, 10, 13, @.on
> > exec sp_trace_setevent @.TraceID, 10, 14, @.on
> > exec sp_trace_setevent @.TraceID, 10, 15, @.on
> > exec sp_trace_setevent @.TraceID, 10, 21, @.on
> > exec sp_trace_setevent @.TraceID, 10, 26, @.on
> > exec sp_trace_setevent @.TraceID, 10, 35, @.on
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture SP:Completed
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 43, 1, @.on
> > exec sp_trace_setevent @.TraceID, 43, 2, @.on
> > exec sp_trace_setevent @.TraceID, 43, 3, @.on
> > exec sp_trace_setevent @.TraceID, 43, 8, @.on
> > exec sp_trace_setevent @.TraceID, 43, 9, @.on
> > exec sp_trace_setevent @.TraceID, 43, 10, @.on
> > exec sp_trace_setevent @.TraceID, 43, 11, @.on
> > exec sp_trace_setevent @.TraceID, 43, 12, @.on
> > exec sp_trace_setevent @.TraceID, 43, 13, @.on
> > exec sp_trace_setevent @.TraceID, 43, 14, @.on
> > exec sp_trace_setevent @.TraceID, 43, 15, @.on
> > exec sp_trace_setevent @.TraceID, 43, 21, @.on
> > exec sp_trace_setevent @.TraceID, 43, 26, @.on
> > exec sp_trace_setevent @.TraceID, 43, 35, @.on
> >
> > --++++++++++++++++++++++++++++++++++++++
> > --Capture SQL:BatchCompleted
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 12, 1, @.on
> > exec sp_trace_setevent @.TraceID, 12, 2, @.on
> > exec sp_trace_setevent @.TraceID, 12, 3, @.on
> > exec sp_trace_setevent @.TraceID, 12, 8, @.on
> > exec sp_trace_setevent @.TraceID, 12, 9, @.on
> > exec sp_trace_setevent @.TraceID, 12, 10, @.on
> > exec sp_trace_setevent @.TraceID, 12, 11, @.on
> > exec sp_trace_setevent @.TraceID, 12, 12, @.on
> > exec sp_trace_setevent @.TraceID, 12, 13, @.on
> > exec sp_trace_setevent @.TraceID, 12, 14, @.on
> > exec sp_trace_setevent @.TraceID, 12, 15, @.on
> > exec sp_trace_setevent @.TraceID, 12, 21, @.on
> > exec sp_trace_setevent @.TraceID, 12, 26, @.on
> > exec sp_trace_setevent @.TraceID, 12, 35, @.on
> >
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture Generic Information
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 70, 1, @.on
> > exec sp_trace_setevent @.TraceID, 70, 2, @.on
> > exec sp_trace_setevent @.TraceID, 70, 3, @.on
> > exec sp_trace_setevent @.TraceID, 70, 8, @.on
> > exec sp_trace_setevent @.TraceID, 70, 9, @.on
> > exec sp_trace_setevent @.TraceID, 70, 10, @.on
> > exec sp_trace_setevent @.TraceID, 70, 11, @.on
> > exec sp_trace_setevent @.TraceID, 70, 12, @.on
> > exec sp_trace_setevent @.TraceID, 70, 14, @.on
> > exec sp_trace_setevent @.TraceID, 70, 21, @.on
> > exec sp_trace_setevent @.TraceID, 70, 26, @.on
> > exec sp_trace_setevent @.TraceID, 70, 35, @.on
> > exec sp_trace_setevent @.TraceID, 71, 1, @.on
> > exec sp_trace_setevent @.TraceID, 71, 2, @.on
> > exec sp_trace_setevent @.TraceID, 71, 3, @.on
> > exec sp_trace_setevent @.TraceID, 71, 8, @.on
> > exec sp_trace_setevent @.TraceID, 71, 9, @.on
> > exec sp_trace_setevent @.TraceID, 71, 10, @.on
> > exec sp_trace_setevent @.TraceID, 71, 11, @.on
> > exec sp_trace_setevent @.TraceID, 71, 12, @.on
> > exec sp_trace_setevent @.TraceID, 71, 14, @.on
> > exec sp_trace_setevent @.TraceID, 71, 21, @.on
> > exec sp_trace_setevent @.TraceID, 71, 26, @.on
> > exec sp_trace_setevent @.TraceID, 71, 35, @.on
> > exec sp_trace_setevent @.TraceID, 72, 1, @.on
> > exec sp_trace_setevent @.TraceID, 72, 2, @.on
> > exec sp_trace_setevent @.TraceID, 72, 3, @.on
> > exec sp_trace_setevent @.TraceID, 72, 8, @.on
> > exec sp_trace_setevent @.TraceID, 72, 9, @.on
> > exec sp_trace_setevent @.TraceID, 72, 10, @.on
> > exec sp_trace_setevent @.TraceID, 72, 11, @.on
> > exec sp_trace_setevent @.TraceID, 72, 12, @.on
> > exec sp_trace_setevent @.TraceID, 72, 14, @.on
> > exec sp_trace_setevent @.TraceID, 72, 21, @.on
> > exec sp_trace_setevent @.TraceID, 72, 26, @.on
> > exec sp_trace_setevent @.TraceID, 72, 35, @.on
> > exec sp_trace_setevent @.TraceID, 74, 1, @.on
> > exec sp_trace_setevent @.TraceID, 74, 2, @.on
> > exec sp_trace_setevent @.TraceID, 74, 3, @.on
> > exec sp_trace_setevent @.TraceID, 74, 8, @.on
> > exec sp_trace_setevent @.TraceID, 74, 9, @.on
> > exec sp_trace_setevent @.TraceID, 74, 10, @.on
> > exec sp_trace_setevent @.TraceID, 74, 11, @.on
> > exec sp_trace_setevent @.TraceID, 74, 12, @.on
> > exec sp_trace_setevent @.TraceID, 74, 14, @.on
> > exec sp_trace_setevent @.TraceID, 74, 21, @.on
> > exec sp_trace_setevent @.TraceID, 74, 26, @.on
> > exec sp_trace_setevent @.TraceID, 74, 35, @.on
> > exec sp_trace_setevent @.TraceID, 77, 1, @.on
> > exec sp_trace_setevent @.TraceID, 77, 2, @.on
> > exec sp_trace_setevent @.TraceID, 77, 3, @.on
> > exec sp_trace_setevent @.TraceID, 77, 8, @.on
> > exec sp_trace_setevent @.TraceID, 77, 9, @.on
> > exec sp_trace_setevent @.TraceID, 77, 10, @.on
> > exec sp_trace_setevent @.TraceID, 77, 11, @.on
> > exec sp_trace_setevent @.TraceID, 77, 12, @.on
> > exec sp_trace_setevent @.TraceID, 77, 14, @.on
> > exec sp_trace_setevent @.TraceID, 77, 21, @.on
> > exec sp_trace_setevent @.TraceID, 77, 26, @.on
> > exec sp_trace_setevent @.TraceID, 77, 35, @.on
> > exec sp_trace_setevent @.TraceID, 78, 1, @.on
> > exec sp_trace_setevent @.TraceID, 78, 2, @.on
> > exec sp_trace_setevent @.TraceID, 78, 3, @.on
> > exec sp_trace_setevent @.TraceID, 78, 8, @.on
> > exec sp_trace_setevent @.TraceID, 78, 9, @.on
> > exec sp_trace_setevent @.TraceID, 78, 10, @.on
> > exec sp_trace_setevent @.TraceID, 78, 11, @.on
> > exec sp_trace_setevent @.TraceID, 78, 12, @.on
> > exec sp_trace_setevent @.TraceID, 78, 14, @.on
> > exec sp_trace_setevent @.TraceID, 78, 21, @.on
> > exec sp_trace_setevent @.TraceID, 78, 26, @.on
> > exec sp_trace_setevent @.TraceID, 78, 35, @.on
> > exec sp_trace_setevent @.TraceID, 100, 1, @.on
> > exec sp_trace_setevent @.TraceID, 100, 2, @.on
> > exec sp_trace_setevent @.TraceID, 100, 3, @.on
> > exec sp_trace_setevent @.TraceID, 100, 8, @.on
> > exec sp_trace_setevent @.TraceID, 100, 9, @.on
> > exec sp_trace_setevent @.TraceID, 100, 10, @.on
> > exec sp_trace_setevent @.TraceID, 100, 11, @.on
> > exec sp_trace_setevent @.TraceID, 100, 12, @.on
> > exec sp_trace_setevent @.TraceID, 100, 14, @.on
> > exec sp_trace_setevent @.TraceID, 100, 21, @.on
> > exec sp_trace_setevent @.TraceID, 100, 26, @.on
> > exec sp_trace_setevent @.TraceID, 100, 35, @.on
> >
> > -- Set the Filters
> > declare @.intfilter int
> > declare @.bigintfilter bigint
> >
> > -- set database filter if @.dbname is not null
> > if @.dbname is not null
> > begin
> > -- check @.dbname is on the server
> > if (select db_id(@.dbname)) is not null
> > set @.intfilter = db_id(@.dbname)
> > exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> > end
> >
> > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> >
> > -- set filter for Long Running Query(LRQ) Duration
> > -- Added this 22/12/2004
> > select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
> > milliseconds(ms)
> > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> >
> >
> > -- 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:
> > -- display Trace information (can output this to a logfile in sqlagent)
> > select * from ::fn_trace_getinfo(default)
> >
> > -- clear down temp table
> > if (select object_id('tempdb.dbo.##fileexists') from
> > tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> > drop table ##fileexists
> > GO
> >
> > "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> > news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> > > Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT
before,
> > and
> > > got error message saying that it needed to be BIGINT.
> > >
> > > "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter,
Line
> > 272
> > > Procedure expects parameter '@.value' of type 'bigint'."
> > >
> > > "Olu Adedeji" wrote:
> > >
> > > > the data type of the @.durationfiltervalue must correspond with the
data
> > type
> > > > of the column to be filtered. otherwise it will not work.
> > > >
> > > > unfortunately I was unable to post this into a QA session my
connection
> > kept
> > > > crashing .
> > > >
> > > > Hope that helps
> > > >
> > > > "Wanderer" wrote:
> > > >
> > > > > Warning - this is quite a LONG post.
> > > > >
> > > > > Hi all,
> > > > >
> > > > > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the
> > picture - I
> > > > > want to do some tracing in our production environment, and have
jobs
> > set up
> > > > > to automaGically run the traces, so I am building the traces in an
SP,
> > via
> > > > > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get
> > called from
> > > > > SQL scheduled jobs, and will happily create the trace files. I
have
> > another
> > > > > SP to then copy these files to another server, and automaGically
> > import them
> > > > > in some tables for analyzing.
> > > > >
> > > > > All good and well, so far, EXCEPT that while my trace get's
created
> > with no
> > > > > errors, when I look at the trace data, it seems that the filters
are
> > not
> > > > > being applied. When I open it up from Profiler, I can see the
trace
> > > > > properties, and the Events and Data columns are all correct, but
there
> > are no
> > > > > Filters. Anyone see where I am going wrong?
> > > > >
> > > > > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > > > > The SP
> > > > >
> > > > > if exists (select 1 from sysobjects where name => > > > > 'Up_Auto_Profiler_Perf') drop procedure
Up_Auto_Profiler_Perfgocreate
> > > > > procedure Up_Auto_Profiler_Perf @.TraceFile
nvarchar(128), --Nvarchar
> > as
> > > > > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > > > > datetime, --The time to stop the trace. @.MaxSize BigInt =10, --BigInt
> > as
> > > > > required by SP_CREATE_TRACE. The maximum size per
> > > > > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on
(in
> > > > > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > > > > On: 2004-12-28-- Purpose: -- This stored procedure was written to
> > facilitate
> > > > > the creation of profiler traces that write to files.-- --
Example:--
> > exec
> > > > > Up_Auto_Profiler_Perf
> > > > >
> >
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-
> > -- Possible
> > > > > future additions:-- 1> simple enhancement to specify DB to store
data,
> > and
> > > > > table.-- 2> accepting a parameter instead of apply the default
.trc.
> > Use the
> > > > > .trc as default-- 3> implementing default value's for dir's, so
that
> > it can
> > > > > run without parm's ... good or bad...- Change Control: version
1 -
> > Regan
> > > > > Galbraith 2004-12-28-- Creation and adding of comment-- version
1.2 -
> > > > > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to
allow
> > > > > generation of limited data, -- filtering on duration-- Added
Output
> > > > > parametre @.TracId to return for lookup on trace. --AS--Declare
> > Control
> > > > > Variabledeclare @.ReturnCode Int--Declare Option Variables
declare
> > @.Option
> > > > > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > > > > @.ComparisonOperator intdeclare @.ColumnId intdeclare
@.LogicalOperator
> > > > > int--Set Option Variableset @.Option => > 2 --TraceFileRollOver --Specifies
> > > > > that when the max_file_size is reached, the current trace file is
> > closed and
> > > > > a new file is created.--Set Trace Filter to exclude System Ids -
that
> > is
> > > > > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > > > > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator => > 2 --Greater
> > > > > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > > > > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create
> > @.TraceId
> > > > > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect
> > @.ReturnCode=@.@.Errorif
> > > > > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
> > Returned when
> > > > > options specified are incompatible.' if @.ReturnCode = 12 Print
'Error
> > 12 -
> > > > > Cannot create tracefile - check if file already exists, or this
trace
> > already
> > > > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory.
Returned
> > when
> > > > > there is not enough memory to perform the specified action.' if
> > @.ReturnCode => > > > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop
time
> > > > > specified has already happened.' if @.ReturnCode = 15 Print 'Error
15 -
> > > > > Invalid parameters. Returned when the user supplied incompatible
> > > > > parameters.' else Print 'Unexpected and Unknown error In creating
> > trace -
> > > > > Please review' Goto ErrorHandlerEnd--Populate Trace with
> > Events--SECTION
> > > > > CURSORS-- this set is : cursor executeset @.EventId = 74exec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor
Openset
> > > > > @.EventId = 53exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this
set is
> > :
> > > > > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND
WARNINGS--
> > this set
> > > > > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set
is :
> > > > > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
> > Lock:DeadLockChainset
> > > > > @.EventId = 59exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
Lock:Timeoutset
> > @.EventId
> > > > > = 27exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this
set is
> > :
> > > > > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset
> > @.EventId => > > > > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan
> > Statistics--
> > > > > set @.EventId = 98-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan
> > Text-- set
> > > > > @.EventId = 96-- exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryData--
> > > > > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass--
exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION
STORED
> > > > > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData--this set is
SP:RPC:Completedset
> > @.EventId
> > > > > = 10exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set
is :
> > SQL
> > > > > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is
:
> > SQL
> > > > > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,17,@.On --Writes-- --this set is :
> > SQL:StmtCompleted-- set
> > > > > @.EventId = 41-- exec sp_trace_setevent
> > @.TraceId,@.EventId,18,@.On --cpu-- exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus
> > @.TraceId,1select
> > > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13
Print
> > > > > 'ERROR 13 - Out of memory. Returned when there is not enough
memory to
> > > > > perform the specified action.' else if @.ReturnCode = 9 Print
'ERROR
> > 9 -
> > > > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
> > print
> > > > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR
1 -
> > Unknown
> > > > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > > >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1
print
> > 'ERROR
> > > > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
trace
> > is
> > > > > currently running. Changing the trace at this time will result in
an
> > > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified
Column
> > is not
> > > > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
Column
> > is not
> > > > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
The
> > > > > specified Comparison Operator is not valid. ' else if @.ReturnCode
= 7
> > print
> > > > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
> > @.ReturnCode
> > > > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.'
else if
> > > > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when
there
> > is not
> > > > > enough memory to perform the specified action.' else if
@.ReturnCode => > > > > 16 print 'ERROR 16 - The function is not valid for this trace.'
else
> > Print
> > > > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif
@.DurationFilterValue
> > <>
> > > > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > > > --Greater than exec sp_trace_setfilter
> > > > >
> >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > select
> > > > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1
print
> > > > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR
2 -
> > The
> > > > > trace is currently running. Changing the trace at this time will
> > result in an
> > > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified
Column
> > is
> > > > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
> > Column
> > > > > is not allowed for filtering.' else if @.ReturnCode = 6 print
'ERROR
> > 6 -
> > > > > The specified Comparison Operator is not valid. ' else if
@.ReturnCode
> > => > > > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.'
else
> > if
> > > > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > > > Returned when there is not enough memory to perform the specified
> > > > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function
is
> > not
> > > > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > > > >
> > > > >
> > > > > The Code that calls the trace
> > > > >
> > > > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime
DATETIMEDECLARE
> > > > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
> > INTEGERDECLARE
> > > > > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error
VariablesSET
> > > > > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control
> > Variablesset
> > > > > @.MinutesToRun = 5 -- this is how long the trace will run forset
> > @.MaxSize => > > > > 100 -- this is the maximum size for a file, in MBset
@.DurationFilter => > > > > 1000--Set Running Variables--the time the trace will stopset
> > @.RunStopTime => > > > > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full
name
> > (not
> > > > > UNC)set @.FileName => > > > > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > > > > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > > > > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > > > > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > > > > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > > > > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > > > > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > > > > varchar(20))--Create trace, writing out to tracefile, until
> > endtimeexec
> > > > > @.Rc=Up_Auto_Profiler_Perf
> > > > > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID
output--Error
> > > > > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > > > > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR
> > ('Create/Run of
> > > > > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> > > > >
> > > > >
> > > > > Ta...
> > > > >
> >
> >
> >|||Thanks Olu, for the responses - just realized I hadn't been polite enough to
acknowledge them!
I must have mis-understood yuo last mail - I thought you were proposing I
use your SP, or check how you are using the SP_TRACE_SETFILTER.
The specific Traces I am trying to turn on are:
set @.LogicalOperator = 0 --and (1 = OR)
set @.columnId = 13 --Duration
set @.ComparisonOperator = 2 --Greater than
@.DurationFilterValue - I am passing in 1000, i.e. 1 second
I see you are using comparison operator of 4 - greater than or equal to - I
will try that and see if that is somehopw causing the issue. If not, I will
create the trace via profiler, and then try and gen the trace and see what's
what.
Thanks for the help so far!!!
"Olu Adedeji" wrote:
> Wanderer,
> as per my email, I was intimating that you open profiler and set the filter
> manually, then script out the trace and cut the section that performs the
> sp_trace_setfilter into your procedure substituting your parameters for
> hardcoded values. then run your procedure and check the trc files to see if
> the traces have been set. . from your post it is obvious that profiler does
> not understand the parameters you have passed into sp_trace_setfilter and as
> a result it is unable to present it in the trc file. PS. out of interest
> what are you trying to filter?
>
> PS. I don't dispute that your trace runs fine . . I am merely trying to
> help resolve the problem
>|||Hmmm- there looks like mine:
--Profiler's trace
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
set @.bigintfilter = 1000
exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
set @.intfilter = 100
exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
--My trace
set @.LogicalOperator = 0
set @.columnId = 13
set @.ComparisonOperator = 4
@.DurationFilterValue = 1000 (bigint)
exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
which is effectively
exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
I'll see if the hardcoding works better ... sighs
... and it would seem it doesn't! Grrr
"Wanderer" wrote:
> Thanks Olu, for the responses - just realized I hadn't been polite enough to
> acknowledge them!
> I must have mis-understood yuo last mail - I thought you were proposing I
> use your SP, or check how you are using the SP_TRACE_SETFILTER.
> The specific Traces I am trying to turn on are:
> set @.LogicalOperator = 0 --and (1 = OR)
> set @.columnId = 13 --Duration
> set @.ComparisonOperator = 2 --Greater than
> @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> I see you are using comparison operator of 4 - greater than or equal to - I
> will try that and see if that is somehopw causing the issue. If not, I will
> create the trace via profiler, and then try and gen the trace and see what's
> what.
> Thanks for the help so far!!!
> "Olu Adedeji" wrote:
> > Wanderer,
> >
> > as per my email, I was intimating that you open profiler and set the filter
> > manually, then script out the trace and cut the section that performs the
> > sp_trace_setfilter into your procedure substituting your parameters for
> > hardcoded values. then run your procedure and check the trc files to see if
> > the traces have been set. . from your post it is obvious that profiler does
> > not understand the parameters you have passed into sp_trace_setfilter and as
> > a result it is unable to present it in the trc file. PS. out of interest
> > what are you trying to filter?
> >
> >
> > PS. I don't dispute that your trace runs fine . . I am merely trying to
> > help resolve the problem
> >
>|||sure thing! I hope that works. . .
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> Hmmm- there looks like mine:
> --Profiler's trace
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> set @.bigintfilter = 1000
> exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> set @.intfilter = 100
> exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> --My trace
> set @.LogicalOperator = 0
> set @.columnId = 13
> set @.ComparisonOperator = 4
> @.DurationFilterValue = 1000 (bigint)
> exec sp_trace_setfilter
>
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> which is effectively
> exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> I'll see if the hardcoding works better ... sighs
> ... and it would seem it doesn't! Grrr
>
> "Wanderer" wrote:
> > Thanks Olu, for the responses - just realized I hadn't been polite
enough to
> > acknowledge them!
> >
> > I must have mis-understood yuo last mail - I thought you were proposing
I
> > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> >
> > The specific Traces I am trying to turn on are:
> >
> > set @.LogicalOperator = 0 --and (1 = OR)
> > set @.columnId = 13 --Duration
> > set @.ComparisonOperator = 2 --Greater than
> > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> >
> > I see you are using comparison operator of 4 - greater than or equal
to - I
> > will try that and see if that is somehopw causing the issue. If not, I
will
> > create the trace via profiler, and then try and gen the trace and see
what's
> > what.
> >
> > Thanks for the help so far!!!
> >
> > "Olu Adedeji" wrote:
> >
> > > Wanderer,
> > >
> > > as per my email, I was intimating that you open profiler and set the
filter
> > > manually, then script out the trace and cut the section that performs
the
> > > sp_trace_setfilter into your procedure substituting your parameters
for
> > > hardcoded values. then run your procedure and check the trc files to
see if
> > > the traces have been set. . from your post it is obvious that profiler
does
> > > not understand the parameters you have passed into sp_trace_setfilter
and as
> > > a result it is unable to present it in the trc file. PS. out of
interest
> > > what are you trying to filter?
> > >
> > >
> > > PS. I don't dispute that your trace runs fine . . I am merely trying
to
> > > help resolve the problem
> > >
> >|||Hmm - not working.
When I run:
SELECT * FROM ::fn_trace_getfilterinfo(1)
I get 0 rows, that implies to me that the filters are not getting created.
Can you confirm on a test that you DO get something from this?
Thanks
"Olu Adedeji" wrote:
> sure thing! I hope that works. . .
> "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> > Hmmm- there looks like mine:
> > --Profiler's trace
> > -- Set the Filters
> > declare @.intfilter int
> > declare @.bigintfilter bigint
> >
> > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> > set @.bigintfilter = 1000
> > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> >
> > set @.intfilter = 100
> > exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> >
> > --My trace
> > set @.LogicalOperator = 0
> > set @.columnId = 13
> > set @.ComparisonOperator = 4
> > @.DurationFilterValue = 1000 (bigint)
> > exec sp_trace_setfilter
> >
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > which is effectively
> > exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> >
> > I'll see if the hardcoding works better ... sighs
> >
> > ... and it would seem it doesn't! Grrr
> >
> >
> >
> > "Wanderer" wrote:
> >
> > > Thanks Olu, for the responses - just realized I hadn't been polite
> enough to
> > > acknowledge them!
> > >
> > > I must have mis-understood yuo last mail - I thought you were proposing
> I
> > > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> > >
> > > The specific Traces I am trying to turn on are:
> > >
> > > set @.LogicalOperator = 0 --and (1 = OR)
> > > set @.columnId = 13 --Duration
> > > set @.ComparisonOperator = 2 --Greater than
> > > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> > >
> > > I see you are using comparison operator of 4 - greater than or equal
> to - I
> > > will try that and see if that is somehopw causing the issue. If not, I
> will
> > > create the trace via profiler, and then try and gen the trace and see
> what's
> > > what.
> > >
> > > Thanks for the help so far!!!
> > >
> > > "Olu Adedeji" wrote:
> > >
> > > > Wanderer,
> > > >
> > > > as per my email, I was intimating that you open profiler and set the
> filter
> > > > manually, then script out the trace and cut the section that performs
> the
> > > > sp_trace_setfilter into your procedure substituting your parameters
> for
> > > > hardcoded values. then run your procedure and check the trc files to
> see if
> > > > the traces have been set. . from your post it is obvious that profiler
> does
> > > > not understand the parameters you have passed into sp_trace_setfilter
> and as
> > > > a result it is unable to present it in the trc file. PS. out of
> interest
> > > > what are you trying to filter?
> > > >
> > > >
> > > > PS. I don't dispute that your trace runs fine . . I am merely trying
> to
> > > > help resolve the problem
> > > >
> > >
>
>|||Ok - so it seems that, for some reason, the sp_trace_setfilter is not
working. Going to try and build in some logic to catch and check why.
Anything that you can see from my SP, that points the way, I'd appreciate
hearing it.
Have a great New Year's eve, and a peaceful and prosperous new year
"Wanderer" wrote:
> Hmm - not working.
> When I run:
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I get 0 rows, that implies to me that the filters are not getting created.
> Can you confirm on a test that you DO get something from this?
> Thanks
> "Olu Adedeji" wrote:
> > sure thing! I hope that works. . .
> >
> > "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> > news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> > > Hmmm- there looks like mine:
> > > --Profiler's trace
> > > -- Set the Filters
> > > declare @.intfilter int
> > > declare @.bigintfilter bigint
> > >
> > > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> > > set @.bigintfilter = 1000
> > > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> > >
> > > set @.intfilter = 100
> > > exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> > >
> > > --My trace
> > > set @.LogicalOperator = 0
> > > set @.columnId = 13
> > > set @.ComparisonOperator = 4
> > > @.DurationFilterValue = 1000 (bigint)
> > > exec sp_trace_setfilter
> > >
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > > which is effectively
> > > exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> > >
> > > I'll see if the hardcoding works better ... sighs
> > >
> > > ... and it would seem it doesn't! Grrr
> > >
> > >
> > >
> > > "Wanderer" wrote:
> > >
> > > > Thanks Olu, for the responses - just realized I hadn't been polite
> > enough to
> > > > acknowledge them!
> > > >
> > > > I must have mis-understood yuo last mail - I thought you were proposing
> > I
> > > > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> > > >
> > > > The specific Traces I am trying to turn on are:
> > > >
> > > > set @.LogicalOperator = 0 --and (1 = OR)
> > > > set @.columnId = 13 --Duration
> > > > set @.ComparisonOperator = 2 --Greater than
> > > > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> > > >
> > > > I see you are using comparison operator of 4 - greater than or equal
> > to - I
> > > > will try that and see if that is somehopw causing the issue. If not, I
> > will
> > > > create the trace via profiler, and then try and gen the trace and see
> > what's
> > > > what.
> > > >
> > > > Thanks for the help so far!!!
> > > >
> > > > "Olu Adedeji" wrote:
> > > >
> > > > > Wanderer,
> > > > >
> > > > > as per my email, I was intimating that you open profiler and set the
> > filter
> > > > > manually, then script out the trace and cut the section that performs
> > the
> > > > > sp_trace_setfilter into your procedure substituting your parameters
> > for
> > > > > hardcoded values. then run your procedure and check the trc files to
> > see if
> > > > > the traces have been set. . from your post it is obvious that profiler
> > does
> > > > > not understand the parameters you have passed into sp_trace_setfilter
> > and as
> > > > > a result it is unable to present it in the trc file. PS. out of
> > interest
> > > > > what are you trying to filter?
> > > > >
> > > > >
> > > > > PS. I don't dispute that your trace runs fine . . I am merely trying
> > to
> > > > > help resolve the problem
> > > > >
> > > >
> >
> >
> >|||Wanderer wrote:
> Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> working. Going to try and build in some logic to catch and check why.
> Anything that you can see from my SP, that points the way, I'd
> appreciate hearing it.
>
You wouldn't see the filter in Profiler if you opened a TRC file after a
server-side trace. All you see in Profiler is the events captured during
the trace. The other information (filters) are not saved anywhere in the
trace file.
Also, I assume when you ran the following statement, you knew that the
running trace had a handle of 1? It seems unlikely that the handle would
be 1 unless you just cycled the server.
SELECT * FROM ::fn_trace_getfilterinfo(1)
I can't read your script file because of the wrapping. What events did
you capture that are not showing the filter applied?
--
David Gugick
Imceda Software
www.imceda.com|||Hi David, thanks for the response!
Thanks for the heads-up on not being able to see the traces! Learnt
something new, there. The problem is I am attempteing to Filter on duration >
1000, but seem to be getting everything. I really don't want to run in our
production environment and capture everything, I strongly suspect that I will
end up with GB's of tracefiles.
Regards the " SELECT * FROM ::fn_trace_getfilterinfo(1)", I also used:
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceId)
where @.TraceId was the @.TraceID variable used to assign the traces in the
SP, and is an output variable. And still got 0 rows returned.
Here is how I called the SP, and then checked the settings:
--Create trace, writing out to tracefile, until endtime
exec @.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output
--Error Handling
select @.rc
select @.TraceID
SELECT * FROM ::fn_trace_getfilterinfo(1)
SELECT * FROM ::fn_trace_getfilterinfo(default)
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
select * from ::fn_trace_getinfo(1)
select * from ::fn_trace_getinfo(default)
select * from ::fn_trace_getinfo(@.TraceID)
The 3 ::fn_trace_getfilterinfo select ALL return 0 rows, whereas the 3
::fn_trace_getinfo ALL return data about the trace I had just created!
I stringly suspect that either I have somehow failed to exec the
SP_TRACE_SETFILTER (and when debugging it certainly didn't seem like a
problem), or it failed (and I do do fairly complete error checking, per the
error codes listed in the BOL), or else, for some reason, it is just not
being applied.
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Well, I've tried to add steps to check on the adding of the filters, and all
i can say is - it doesn't seem to work!
Here is a section of the code (hopefully the wordwrap doesn't make it
completely illegible).
****code****
exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
select @.ReturnCode=@.@.Error
Print 'Trace Filter 2 set'
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
****code****
I get NO response that an error has occurred, the "print's" confirm that the
code stepped through, yet the ::fn_trace_setfilter returns NO ROWS!
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Found it!!!
*sigh* - a blonde moment!
Just before I add the filters, I exec:
exec sp_trace_setstatus @.TraceId,1
In other words, I start the trace, hence I cannot apply filters. And because
I am checing @.@.error for the exec sp_trace_setfilter, and not doing what I
SHOULD be doing, which is:
exec @.ReturnCode=sp_trace_setfilter ...
I never received the response saying that the filter was running (error 2).
Thanks for your help !
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||OH, btw,
I found that after properly creating those filters, when I open the trace
file created by my SP, in profiler, I DO see the filters under the properties
tab for the trace.
Just FYI
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Wanderer wrote:
> OH, btw,
> I found that after properly creating those filters, when I open the
> trace file created by my SP, in profiler, I DO see the filters under
> the properties tab for the trace.
> Just FYI
>
Strange. I don't see them on some test traces I just ran. Are you saying
that you run a server-side trace, stop the trace, open the trace file in
Profiler, and then see the trace filters? I can't see it here using that
method.
Any verification appreciated.
Thanks
David Gugick|||Hi David,
Using that SP I created, and then fixed :-), I basically run a server side
trace to file. Then I copy the file to another machine, where I do my
analysis.
When opening that trace file, in Profiler, and going to the properties of
the trace, I definitely see my filter.
HTH
"David Gugick" wrote:
> Wanderer wrote:
> > OH, btw,
> >
> > I found that after properly creating those filters, when I open the
> > trace file created by my SP, in profiler, I DO see the filters under
> > the properties tab for the trace.
> >
> > Just FYI
> >
> Strange. I don't see them on some test traces I just ran. Are you saying
> that you run a server-side trace, stop the trace, open the trace file in
> Profiler, and then see the trace filters? I can't see it here using that
> method.
> Any verification appreciated.
> Thanks
>
> --
> David Gugick
>|||Wanderer wrote:
> Hi David,
> Using that SP I created, and then fixed :-), I basically run a server
> side trace to file. Then I copy the file to another machine, where I
> do my analysis.
> When opening that trace file, in Profiler, and going to the
> properties of the trace, I definitely see my filter.
> HTH
> "David Gugick" wrote:
>> Wanderer wrote:
>> OH, btw,
>> I found that after properly creating those filters, when I open the
>> trace file created by my SP, in profiler, I DO see the filters under
>> the properties tab for the trace.
>> Just FYI
>>
>> Strange. I don't see them on some test traces I just ran. Are you
>> saying that you run a server-side trace, stop the trace, open the
>> trace file in Profiler, and then see the trace filters? I can't see
>> it here using that method.
>> Any verification appreciated.
>> Thanks
>>
>> --
>> David Gugick
Can you repost your code so I can see if there are any differences?
--
David Gugick|||Sure. here goes:
****code****
if exists (select 1 from sysobjects where name = 'Up_Auto_Profiler_Perf')
drop procedure Up_Auto_Profiler_Perf
go
create procedure Up_Auto_Profiler_Perf
@.TraceFile nvarchar(128), --Nvarchar as required by SP_CREATE_TRACE. The
File to be created.
@.StopTime datetime, --The time to stop the trace.
@.MaxSize BigInt = 10, --BigInt as required by SP_CREATE_TRACE. The
maximum size per file.
@.DurationFilterValue BIGINT, --The duration to filter on (in ms).
@.TraceID int OUTPUT
-- Create By: Regan Galbraith
-- Create On: 2004-12-28
-- Purpose:
-- This stored procedure was written to facilitate the creation of profiler
traces that write to files.
--
-- Example:
-- exec Up_Auto_Profiler_Perf
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500
--
-- Possible future additions:
-- 1> simple enhancement to specify DB to store data, and table.
-- 2> accepting a parameter instead of apply the default .trc. Use the .trc
as default
-- 3> implementing default value's for dir's, so that it can run without
parm's ... good or bad?
--
-- Change Control: version 1 - Regan Galbraith 2004-12-28
-- Creation and adding of comment
-- version 1.2 - Regan Galbraith 2004-12-29
-- Added @.DurationFilterValue logic to allow generation of limited data,
-- filtering on duration
-- Added Output parametre @.TracId to return for lookup on trace.
-- version 1.3 - Regan Galbraith 2005-01-03
-- Fixed the problems with applying trace filters by correcting error
checking to
-- use return code, and by moving start trace to end, sicne filters
cannot apply
-- to running trace.
--
AS
set nocount on
--Declare Control Variable
declare @.ReturnCode Int
--Declare Option Variables
declare @.Option int
declare @.EventId int
declare @.On bit
declare @.Value int
declare @.ComparisonOperator int
declare @.ColumnId int
declare @.LogicalOperator int
--Set Option Variable
set @.Option = 2 --TraceFileRollOver
--Specifies that when the max_file_size is reached, the current trace file
is closed and a new file is created.
--Set Trace Filter to exclude System Ids - that is ObjectId > 100
set @.Value = 100
set @.ColumnId = 22 --0bjectid
set @.LogicalOperator = 0 --and (1 = OR)
set @.ComparisonOperator = 2 --Greater than
--Set Control Variables
set @.ReturnCode = 0 --No Error
set @.On = 1 --True
--Create a trace, retrieve @.TraceId
exec @.ReturnCode=sp_trace_create @.TraceId
output,@.Option,@.TraceFile,@.MaxSize,@.StopTime
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
Print 'Error 1 - Unknown error.'
if @.ReturnCode = 10
Print 'Error 10 - Invalid options. Returned when options specified are
incompatible.'
if @.ReturnCode = 12
Print 'Error 12 - Cannot create tracefile - check if file already exists,
or this trace already running'
if @.ReturnCode = 13
Print 'Error 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
if @.ReturnCode = 14
Print 'Error 14 - Invalid stop time. Returned when the stop time specified
has already happened.'
if @.ReturnCode = 15
Print 'Error 15 - Invalid parameters. Returned when the user supplied
incompatible parameters.'
else
Print 'Unexpected and Unknown error In creating trace - Please review'
Goto ErrorHandler
End
--Populate Trace with Events
--SECTION CURSORS
-- this set is : cursor execute
set @.EventId = 74
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- this set is : Cursor Open
set @.EventId = 53
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- this set is : Cursor Recompile
set @.EventId = 75
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
--SECTION ERRORS AND WARNINGS
-- this set is : MissingJoinPredicate
set @.EventId = 80
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
--SECTION LOCKS
-- this set is : Lock:DeadLock
set @.EventId = 25
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- this set is : Lock:DeadLockChain
set @.EventId = 59
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- this set is : Lock:Timeout
set @.EventId = 27
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
--SECTION PERFORMANCE
-- this set is : Execution Plan
set @.EventId = 68
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- this set is : Show Plan
-- set @.EventId = 97
-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- --this set is : Show Plan Statistics
-- set @.EventId = 98
-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
--
-- --this set is : Show Plan Text
-- set @.EventId = 96
-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
--SECTION STORED PROCEDURES
--this set is SP:Recompile
set @.EventId = 37
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,29,@.On --NestLevel
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
exec sp_trace_setevent @.TraceId,@.EventId,34,@.On --ObjectName
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
--this set is SP:RPC:Completed
set @.EventId = 10
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,16,@.On --Reads
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
exec sp_trace_setevent @.TraceId,@.EventId,17,@.On --Writes
--SECTION TRANSACTIONS
--this set is : SQL Transaction
-- set @.EventId = 50
-- exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
-- exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,21,@.On --EventSubClass
-- exec sp_trace_setevent @.TraceId,@.EventId,34,@.On --ObjectName
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- exec sp_trace_setevent @.TraceId,@.EventId,4,@.On --TRansactionID
--SECTION TSQL
--this set is : SQL BatchCompleted
set @.EventId = 12
exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,16,@.On --Reads
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
exec sp_trace_setevent @.TraceId,@.EventId,17,@.On --Writes
-- --this set is : SQL:StmtCompleted
-- set @.EventId = 41
-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu
-- exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
-- exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,29,@.On --NestLevel
-- exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- exec sp_trace_setevent @.TraceId,@.EventId,16,@.On --Reads
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- exec sp_trace_setevent @.TraceId,@.EventId,17,@.On --Writes
--Add a trace filter to exclude System Ids - that is ObjectId > 100
exec @.ReturnCode=sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Value
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
if @.DurationFilterValue <> 0
begin
set @.columnId = 13 --Duration
set @.ComparisonOperator = 4 --Greater than or equal to
--Add trace for Trace being create (@.TraceID, on column Duration, Greater
than Equal to, @.DurationFilterValue)
exec @.ReturnCode=sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
end
--Some information on the trace
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
SELECT * FROM ::fn_trace_getinfo(@.TraceID)
Print 'Start Trace'
exec @.ReturnCode=sp_trace_setstatus @.TraceId,1
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 13
Print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 9
Print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 8
print 'ERROR 8 - The specified Status is not valid.'
else
Print 'ERROR 1 - Unknown Error'
GoTo ErrorHandler
end
ErrorHandler:
Return @.ReturnCode
****code****
Code I use to invoke it:
****code****
set nocount on
DECLARE @.FileName NVARCHAR(128)
DECLARE @.RunStopTime DATETIME
DECLARE @.MaxSize BIGINT
DECLARE @.DurationFilter BIGINT
DECLARE @.TraceID INTEGER
DECLARE @.MinutesToRun INTEGER
DECLARE @.RC INTEGER
DECLARE @.DBID INTEGER
DECLARE @.DBNAME NVARCHAR(128)
-- Set Error Variables
SET @.DBNAME = DB_NAME()
SET @.DBID = DB_ID()
SET @.rc = 0
--Set Control Variables
set @.MinutesToRun = 5 -- this is how long the trace will run for
set @.MaxSize = 100 -- this is the maximum size for a file, in MB
set @.DurationFilter = 1000
--Set Running Variables
--the time the trace will stop
set @.RunStopTime = dateadd(mi,@.MinutesToRun,getdate())
--the file to be create - full name (not UNC)
set @.FileName = 'C:\Auto_Prof\AutoProf_SP_'
+cast(datepart(yyyy,getdate()) as char(4)) --Years
+right(cast(datepart(m ,getdate())+100 as char(3)),2) --Months
+right(cast(datepart(d,getdate()) +100 as char(3)),2)+'_' --Days
+right(cast(datepart(hh,getdate())+100 as char(3)),2) --Hours
+right(cast(datepart(mi,getdate())+100 as char(3)),2) --Minutes
--Display variables
print 'File created is : '+@.FileName
print 'End time will be : '+cast(@.RunStopTime as varchar(20))
--Create trace, writing out to tracefile, until endtime
exec @.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
-- select * from ::fn_trace_getinfo(1)
-- select * from ::fn_trace_getinfo(default)
select * from ::fn_trace_getinfo(@.TraceID)
if (@.rc <> 0 )
RAISERROR ('Create/Run of Trace FAILED', 16, 1, @.DBID, @.DBNAME)
GO
****code****
"David Gugick" wrote:
> Can you repost your code so I can see if there are any differences?
> --
> David Gugick
>|||Hello everyone,
I appear to be having the same problem as Wanderer. I would appreciate any
suggestions.
I have a running trace. I stop the trace with
sp_trace_setstatus 1,0
I apply a filter on dbid as follows:
declare @.bigintfilter bigint
select @.bigintfilter = 22
exec sp_trace_setfilter 1,4,0,3,@.bigintfilter
I then check to see if the filter is in place:
select * from ::fn_trace_getfilterinfo(1)
The filter is not listed.
Where am I going wrong?
Thanks in advance. Any help would be appreciated.
Jim|||Check the return code from sp_trace_setfilter! All trace calls you make you
need to check the return code and check it before continuing.
Besides that which trace column is of type bigint?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Jim Strange" <Jim Strange@.discussions.microsoft.com> wrote in message
news:278E6A2B-FDBE-4198-95F5-8E91C2376338@.microsoft.com...
> Hello everyone,
> I appear to be having the same problem as Wanderer. I would appreciate
> any
> suggestions.
> I have a running trace. I stop the trace with
> sp_trace_setstatus 1,0
> I apply a filter on dbid as follows:
> declare @.bigintfilter bigint
> select @.bigintfilter = 22
> exec sp_trace_setfilter 1,4,0,3,@.bigintfilter
> I then check to see if the filter is in place:
> select * from ::fn_trace_getfilterinfo(1)
> The filter is not listed.
> Where am I going wrong?
> Thanks in advance. Any help would be appreciated.
> Jim
>