Posted by michael via SQLMonster.com on 02/28/06 22:41

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.aspx/sql-server-general/200602/1

[Back to original message]