Re: Converting Traceid issue

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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: One Web Service updates SQL, another cant?
    ... section of the Trace Properties dialog. ... And then I start getting the database closed errors on subsequent calls. ... > The error suggests that somehow the connection to SQL Server is being ... Could we have a look at your connection string? ...
    (microsoft.public.sqlserver.security)
  • RE: cannot delete a record in sql server 2005 through asp program
    ... Using trace script he provided and trace file created by the script. ... SQL Server database by the asp application. ... Microsoft Online Community Support ... a Microsoft Support Engineer within 2 business day is acceptable. ...
    (microsoft.public.sqlserver.programming)
  • Re: Profiler Security
    ... There's no way to restrict what a user of trace can see - if they ... etc. coming into SQL Server. ... >> want to assign the developers the sys admin sql role. ...
    (microsoft.public.sqlserver.security)
  • Re: Log everything
    ... > I've been using SQL Server for a while, but I'm fairly new to this ... > I would like to log every error that occurs in every stored procedure ... you research creating a server-side trace. ...
    (microsoft.public.sqlserver.server)
  • Re: Log everything
    ... > I've been using SQL Server for a while, but I'm fairly new to this ... > I would like to log every error that occurs in every stored procedure ... you research creating a server-side trace. ...
    (microsoft.public.sqlserver.programming)