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?