<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Creating and Running a Trace from Stored Proc...
Creating and Running a Trace from Stored Proc
Rate Topic
Display Mode
Topic Options
Author
Message
dwilliscp
dwilliscp
Posted Monday, January 21, 2013 1:24 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187,
Visits: 332
is there some special format you have to put the SQL in, when you try and launch from a stored Proc?
USE [Zemeter.NET]
GO
/****** Object: StoredProcedure [dbo].[zpr_Start_Performance_Trace] Script Date: 01/21/2013 15:06:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
**
** Import MRP_Controller Data for use in Reporting
**
** V001.001 DWP 01/21/2013 Created procedure
**
**
** Input: (None)
**
**
**
** Output: Writes trace file
** C:\\tracefiles\SQL_Performance_ (datetime stamp)
**
**
*/
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----|
create procedure [dbo].[zpr_Start_Performance_Trace2]
AS
declare
@rc int,
@TraceID int,
@maxfilesize bigint,
@EndTime datetime,
@OutputFileName nvarchar(256)
------------------------------------------------------------------------------------- Begin Procedure ----------------------------------------------------------- ----|
/****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 01/21/2013 01:28:42 PM */
/****************************************************/
-- Trace to capture SQL statements and Stored Proc's that are run
-- during the time limit set out in @EndTime
set @maxfilesize = 100
set @OutputFileName = 'C:\\tracefiles\SQL_Performance' + '_' + convert(varchar(20), getdate(),112) + Replace(convert(varchar(20), getdate(),108),':','')
set @EndTime = dateadd(mi,60,getdate())
exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, @EndTIme
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 64, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 49, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 60, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 64, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 49, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 60, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
GO
--------------------
I get the following erros when I exec the Proc
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setstatus, Line 1
Could not find the requested trace.
Post #1409692
GilaMonster
GilaMonster
Posted Monday, January 21, 2013 1:57 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669,
Visits: 29,923
You've got double slashes in your path.
set @OutputFileName =
'C:\\
tracefiles\SQL_Performance' + '_' + convert(varchar(20), getdate(),112) + Replace(convert(varchar(20), getdate(),108),':','')
The first error is 'could not create trace file'.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1409706
dwilliscp
dwilliscp
Posted Tuesday, January 22, 2013 7:02 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187,
Visits: 332
Thanks.. man I can not tell you how many times I read through that and did not see the problem.
Post #1410016
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
