error with sp_trace_create

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 12, 2010 at 8:43 am

  #220564

  my blinders are on this morning; I'm having a basic syntax error with sp_trace_create , where i'm trying to supply all the parameters.

  i'm getting this error:

  Msg 19065, Level 16, State 1, Procedure sp_trace_create, Line 1

  The parameter is not valid.

  and here is the code I'm using:

  --declare variables for parameterizing the command

  declare @traceidout int

  declare @options int

  declare @path nvarchar(256)

  declare @maxfilesize bigint

  declare @maxRolloverFiles int

  declare @stoptime datetime

  declare @on bit

  --------

  --definition from BOL:

  --sp_trace_create

  --[ @traceid = ] trace_id OUTPUT

  --, [ @options = ] option_value

  --, [ @tracefile = ] 'trace_file'

  --[ , [ @maxfilesize = ] max_file_size ]

  --[ , [ @stoptime = ] 'stop_time' ]

  --[ , [ @filecount = ] 'max_rollover_files' ]

  --------

  set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.

  set @maxfilesize = 20 --size in MB

  set @maxRolloverFiles = 5 --number of files; ie if 5 files, start rewriting on rollover

  set @stoptime = NULL -- null if never ends, else a specific date

  set @options = 0

  set @path = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\mytrace' -- the trace adds ".trc" to the pathname, so avoiad "name.trc.trc" by removing it for scripting

  --create the trace

  exec sp_trace_create

  @traceid = @traceidout output,

  @options = @options,

  @tracefile = @path,

  @maxfilesize = @maxfilesize,

  @stoptime = @stoptime,

  @filecount = @maxRolloverFiles

  If it matters, I get the same error on either a 2005 or 2008 instance. The reason I need this, is I'm testing my prototype for reverse engineering a trace, because of this post here, and the results looked right, but fails to run when pasted toa new window, due to this error i guess.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 12, 2010 at 8:56 am

  #1118082

  nevermind.

  my error was this:

  set @options = 0

  the allowed values for @options is 2 in my case.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!