Posted by Erland Sommarskog on 03/01/06 00:47

michael via SQLMonster.com (u13012@uwe) writes:
> 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
> minutes, the job is supposed to stop the trace, clear it from memory,
> rename the trace file, and start a new trace so I can select the average
> duration for this process. I am getting the following error message:
>
> Procedure expects parameter '@traceid' of type 'int'
>
> When I try to run this portion of the script (@traceid is declared as an
> INT at the beginning of the job):
>
> Set @traceid = (select distinct(convert(int,traceid)) from
> ::fn_trace_getinfo> (default) where value =
> 'D:\MSSQL\JOBS\HCMDB_RequestQueue_Trace.trc') -- the
> name of my trace file
>
> print 'Stop current trace'
> exec sp_trace_setstatus @traceid,0
>...
> 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

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]