Post reply

Scripting a profile trace with CHANGEABLE file name

  • May 7, 2009 at 7:11 am

    #209152

    Hi its only the following bit where I am struggling

    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @Datetime

    I would like to replace InsertFileNameHere with a @variable, but I can not get it to work.

    Please could someone help me with the syntax?

    Many thanks

  • May 7, 2009 at 7:52 am

    #990350

    Declare @filename nvarchar(245),

    @rc Int,

    @TraceID Int,

    @maxfilesize Int,

    @DateTime DateTime

    Set @filename = 'FilePath\FileName'

    Set @maxfilesize = 20

    Set @DateTime = DateAdd(Day, 1, GetDate())

    exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize, @Datetime

    Select @TraceID as traceid

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 7, 2009 at 7:54 am

    #990354

    Ive got a stored procedure which does it; is this what you want?

    --we want the current trace folder

    declare @path nvarchar(256)

    --ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

    SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1

    SET @path = @path + N'MyDMLtrace' --system appends .trc automatically for the filename

    --create the trace

    exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 7, 2009 at 10:14 am

    #990480

    Great help people. Many thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply