March 22, 2004 at 2:40 pm
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:
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
March 22, 2004 at 2:55 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 22, 2004 at 3:03 pm
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
March 22, 2004 at 3:15 pm
Try this....
declare @traceid int
exec sp_trace_create @traceid output, 8
March 22, 2004 at 3:41 pm
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:
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ @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
March 22, 2004 at 4:02 pm
@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.
March 24, 2004 at 4:06 am
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
March 24, 2004 at 5:48 am
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.
March 24, 2004 at 10:16 am
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_)
March 24, 2004 at 10:53 am
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
March 24, 2004 at 11:08 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply