Syntax help requested

Question

Syntax help requested

Gary Andrews

SSCommitted

Points: 1639
More actions
March 22, 2004 at 2:40 pm

#107954

In Query Analyzer I enter the following:
master..sp_trace_create @options = 8
I get the following message
Server: Msg 201, Level 16, State 17, Procedure sp_trace_create, Line 1
Procedure 'sp_trace_create' expects parameter '@options', which was not supplied.
BOL shows the following:
Syntax
sp_trace_create [ @traceid = ] trace_id OUTPUT
    , [ @options = ] option_value
    , [ @tracefile = ] 'trace_file'
    [ , [ @maxfilesize = ] max_file_size ]
    [ , [ @stoptime = ] 'stop_time' ]
Where am I going wrong?
TIA
GaryA

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

Sorry, can't dig too deep right now, but this occurs for me. I suspect that more of these parameters are not optional and must be included.

Gary Andrews SSCommitted Points: 1639 More actions · Answer 2

Thanks Steve. After re-visiting question/coding, I think I see how you could arrive at that conclusion.

I will dig a little deeper.

Thanks again.

GaryA

Bavineni SSC Enthusiast Points: 147 More actions · Answer 3

Try this....

declare @traceid int

exec sp_trace_create @traceid output, 8

Gary Andrews SSCommitted Points: 1639 More actions · Answer 4

Thanks Bavineni, that worked. I think Steve may be right in his previous reply.

I would also ask for some help in interpreting BOL.

BOL say the following:

Syntax

sp_trace_create [ @traceid = ] trace_id OUTPUT

, [ @options = ] option_value

, [ @tracefile = ] 'trace_file'

[ , [ @maxfilesize = ] max_file_size ]

[ , [ @stoptime = ] 'stop_time' ]

Arguments

[ @traceid = ] trace_id

1. Is the number assigned by Microsoft® SQL Server™ 2000 to the new trace.

2. Any user-provided input will be ignored. trace_id is int, with a default of NULL.

3. The user employs the trace_id value to identify, modify, and control the trace defined by this stored procedure.

------------------------------

Now for my question.

Item one says SQL Server uses this number (trace-id) to assign to the trace. So far, so good.

Item two says it is ignoring user provided input (trace-id). This seems like a direct contradiction to item one.

Item three indicates the user has some control via the number (trace-id). Again, if item two is true, then this seems like a contradiction to me.

TIA

GaryA

bp SSCarpal Tunnel Points: 4656 More actions · Answer 5

@traceid is returned to you. You cannot assign a value to @traceid. @traceid is returned to you because you need @traceid to start the trace you just created (eg. EXEC sp_trace_setstatus @TraceID, 1). By default, when you create the trace, the status is in stopped stated.

stax68 SSChampion Points: 11711 More actions · Answer 6

The syntax quoted from BOL shows that the first three params are mandatory: they aren't contained in square brackets.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Antares686 SSC Guru Points: 125444 More actions · Answer 7

However you do have to create a variable and put in thet position with the word OUTPUT behind it to return you the value.

declare @x int

exec sp_trace_setstatus @x OUTPUT, 1

select @x -- Will return you the new id for the trace so you can manually stop it.

Peter Evans Ten Centuries Points: 1187 More actions · Answer 8

All Item 2 states is that the @traceid is used as a output only value. SQL doesn't have true out only parameters. 2 says the sp will ignore the the input value.

declare @in_traceid int

select @in_traceid=1234 -- my tracedid

exec sp_trace_create @in_traceid, 8, '.\traceid.results'+convert(char(10), @in_traceid)

select @in_traceid -- value not guaranteed to still be 1234.

Peter Evans (__PETER Peter_)

Gary Andrews SSCommitted Points: 1639 More actions · Answer 9

Many thanks to all who helped me solve this problem.

I must confess my ignorance caused my grief.

The OUTPUT specification clearly, if one is familiar with procedure definition/construction, tells you that the procedure is returning a value (i.e. that this is not an input parameter).

Once this is embedded in your consciousness, then the BOL definition makes sense and is easily followed.

Now declaring/defining the OUTPUT variable, hopefully becomes obvious, with being able to get the returned procedure value.

Again, many thanks for the help!!!

GaryA

SQLBill SSC Guru Points: 51440 More actions · Answer 10

Gary,

To (hopefully) add to your understanding. If you could 'set' the TraceID, then you could accidentally assign the same ID to two different traces. So, the trace create command won't allow you to set the ID number. However, it will OUTPUT what SQL Server sets the ID to. That way you know the TraceID and can use it in other commands, such as when you stop the trace. You need to know the TraceID to stop it.

-SQLBill