Converting Traceid issue

[michael via SQLMonster.com]

Hi,

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

print 'Erase current trace from memory'
exec sp_trace_setstatus @traceid,2

print 'Moving file to _1'
exec master..xp_cmdshell 'move D:\MSSQL\JOBS\HCMDB RequestQueue Trace.trc D:\
MSSQL\JOBS\HCMDB_RequestQueue_Trace1.trc',
NO_OUTPUT

I know I must be missing something obvious, but I haven't been able to figure
it out. Any assistance is greatly appreciated.

Thanks,
Michael

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200602/1

[Erland Sommarskog]

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[michaelgangeri via SQLMonster.com]

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

[michael via SQLMonster.com]

Okay, looks like I've got it working now. I needed to use CAST instead of
CONVERT for the traceid to get it to read as an INT.

Thanks again for the feedback.

michael wrote:

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

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 - 19 lines]

I would recommand to explicitly convert value to nvarchar(4000).
is sql_variaamt

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