CODEWITHSUNDEEP
t-sqlsql-server-2005stack-trace
Question3CPO
Question3CPO

Reputation: 1202

SQL Server 2005 Trace Error: "The parameter is not valid."

I'm running a server side trace on SQL Server 2005 Enterprise and am receiving the below error:

Msg 19064, Level 16, State 1, Procedure sp_trace_create, Line 1
The requested trace stop time has been already passed.

I'm using this information as a test for this trace script, before I execute a formal server side trace. According to the site, I need a DATETIME variable (in this case @stop) which, if NULL, will continue endlessly. Below this is the pertinent code to this:

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @end datetime     

set @maxfilesize = 20
set @end = NULL

exec @rc = sp_trace_create 
    @TraceID output
    , 0
    , N'\\MyFilePath\trace'
    , @maxfilesize
    , @end
    , 100
if (@rc != 0) goto error

I also receive this same error message if I take the suggestion from the site about doing a time frame (like two hours), or if I do NULL (see above this). I do not receive an error if I set the parameter to 100, for instance, but I get another error message that states the following:

Msg 19064, Level 16, State 1, Procedure sp_trace_create, Line 1 The requested trace stop time has been already passed.

On top of that error, I don't receive any output files like this trace should generate. Note, that I am assuming that the parameter which is not valid is @end because when I change it to - what should be invalid - other values, it executes with a different error.

The remaining code, which shouldn't be pertinent to this error, is below:

-- Trace Events: REMOVED
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 9cab2330-a33d-40d4-be59-cf5def384983'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
--sp_trace_setstatus  @traceid =  2,  @status =  0    -- Trace stop

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Upvotes: 1

Views: 866

Answers (2)

Isaac
Isaac

Reputation: 79

Set to 0 param to 2.

exec @rc = sp_trace_create 
    @TraceID output
    , 2 -- THIS CHANGED FROM 0 to 2
    , N'\\MyFilePath\trace'
    , @maxfilesize
    , @end
    , 100 -- THIS NO LONGER GENERATES THE ERROR

Upvotes: 1

Question3CPO
Question3CPO

Reputation: 1202

My mistake, which was included on the site:

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @end datetime     

set @maxfilesize = 20
set @end = NULL

exec @rc = sp_trace_create 
    @TraceID output
    , 0
    , N'\\MyFilePath\trace'
    , @maxfilesize
    , @end
    , 100 -- THIS GENERATES THE ERROR
if (@rc != 0) goto error

When I remove the , 100, it runs fine. According to the site I originally referenced, this parameter limited the number of files created:

If you want to limit the number of files the trace creates - for example, to 10 files - add 10 to the end of the parameter list . This won’t stop the trace after it creates the number of files given for @ filecount. Instead, when the trace creates a new file, it deletes the oldest file for this trace. So if you start off with Trace_1, Trace2 … Trace_10, and then it creates Trace_11, Trace_1 will be deleted. This keeps you from filling up your hard drive with trace files.

I'll keep researching why this parameter isn't accepted by the system (as it's useful to limit the number of files that are created), it may be a compatibility issue or something related to editions.

Upvotes: 0

Related Questions