Converting Traceid issue

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
.

Relevant Pages

  • SqlDataAdapter.Fill returns results of previous command
    ... This code executes successfully at certain points in the application, ... I don't know what the cause of this exception is yet, ... stored procedure was indeed executed on the database. ... The trace shows that the last action on the db was the stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: fn_trace_gettable threatening my sanity
    ... solution is so ridiculous as to be unbelievable: my stored procedure ... by the procedure that created the trace. ... with the file type added by the ... uppercase and one with lowercase to see the problem behavior and ...
    (microsoft.public.sqlserver.server)
  • Re: Stored Procedures Data view webparts
    ... The only way I have found to get any type of trace is to use SQL Profiler. ... I've had a stored procedure created that works in the following way ... Having the following issues basically i get a generic error message ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Log everything
    ... from the trace I can see that probably an error occurs in a trigger. ... trigger does a rollback tran (and with that makes it impossible to put some ... >> I would like to log every error that occurs in every stored procedure ...
    (microsoft.public.sqlserver.server)
  • Re: Log everything
    ... from the trace I can see that probably an error occurs in a trigger. ... trigger does a rollback tran (and with that makes it impossible to put some ... >> I would like to log every error that occurs in every stored procedure ...
    (microsoft.public.sqlserver.programming)