Why does the order of parameters matter for sp_trace_create?

Question

The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options:

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @options = 0,
  @tracefile = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @tracefile = N'C:\temp\TestTrace',
  @options = 0;

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

The first batch creates a new trace, selects its id, and then closes the trace. One result set is returned:

@new_trace_id
2

The second batch fails with an error:

Msg 214, Level 16, State 3, Procedure sp_trace_create, Line 1 Procedure expects parameter '@tracefile' of type 'nvarchar(256)'.

Why does parameter order affect the output of stored procedure sp_trace_create? And why does it fail with such a misleading error message?

Answer 1

I believe this is because it is an extended stored procedure and the parameter names are actually entirely ignored. It just goes off position.

I have renamed them as below (and given them all the same name) and it still works fine.

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @rubbish = @new_trace_id OUTPUT,
  @rubbish = 0,
  @rubbish = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;

A similar documentation bug was filed by Aaron about sp_executesql.

Another annoying aspect of that stored procedure is that the @maxfilesize must be passed as 'bigint' and it doesn't accept a literal integer. I assume that this is also because it is an extended stored procedure.

Answer 2

This works for me: iff you specify @maxfiles, you must use the option TRACE_FILE_ROLLOVER (= 2):

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @maxfiles int

set @maxfilesize = 2        -- Mb per file
set @maxfiles = 10          -- number of files (10 files x 2Mb --> 20 Mb)

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

-- The option @maxfiles it's only admitted if TRACE_FILE_ROLLOVER  (value 2) is set
exec @rc = sp_trace_create @TraceID output, **2**, N'E:\sql\trazas\traza_a', @maxfilesize, NULL, @maxfiles
if (@rc != 0) goto error

-- Set the events
...

Whith this option (2) there is no error creating the trace