Posted by michaelgangeri via SQLMonster.com on 03/01/06 15:57

Thanks for your response. I think I might not have stated the issue clearly
or am not understanding your response. My issue is that the traceid is not
being returned as an INT. There is no problem with the file name and the
nvarchar.

Of the PRINT statements in my code below, I get everything up to and
including 'Stop current trace'. My SET does return the traceid, but not in
integer format.

I'll do further research on the sql_variant though and see if I can get
further with that.

Thanks

Erland Sommarskog wrote:
>> I am trying to automate a SQL Trace via a stored procedure and a job.
>> The job executes the stored procedure to start the trace and every 15
>[quoted text clipped - 17 lines]
>> I know I must be missing something obvious, but I haven't been able to
>> figure it out. Any assistance is greatly appreciated.
>
>Obvious and obvious... First a hint. Try this:
>
> DECLARE @traceid int
> exec sp_trace_setstatus @traceid,0
>
>This give the same error as you get. sp_trace_setstatus does not
>like the NULL value.
>
>So presumably, you fail to set @traceid. The value column of
>fn_get_tracestatus is sql_variant. Per the conversion rules in
>SQL Server, the string literal is converted to sql_variant. I believe
>that for to sql_variant values to be equal, they must have the same
>base type. But value for the file name, is surely nvarchar.
>
>So adding an N before string literal to make it nvarchar may work.
>I would recommand to explicitly convert value to nvarchar(4000).
>is sql_variaamt
>

--
Message posted via http://www.sqlmonster.com

[Back to original message]