Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
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 Elder Sep 28 '12 at 11:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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