7

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?

6

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.

  • 1
    As Aaron says, it is easier to read and debug a stored procedure whose parameters are specified by name. It's one of my favorite features of T-SQL. What a shame this feature is broken for extended stored procedures! Perhaps this is one reason extended stored procedures are generally deprecated. – Iain Samuel McLean Elder Sep 28 '12 at 11:16
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.