January 21, 2013 at 1:24 pm
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
@rcint,
@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.
January 21, 2013 at 1:57 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 7:02 am
Thanks.. man I can not tell you how many times I read through that and did not see the problem.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy