InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 21:27:31 GMT20http://www.sqlservercentral.com/Forums/Topic617856-359-1.aspxIt looks to me like the trace file ID suffix is being captured in hexadecimal, and needs to be converted to decimal in order for the file name to be found.Tue, 27 Jul 2010 12:22:00 GMTsteve smith-401573http://www.sqlservercentral.com/Forums/Topic617856-359-1.aspxI was hoping someone may have some thoughts how to address an issue I have with a custom BlackBox trace that I need to setup as a startup proc and ensure the trace fiel is unique.The sql blackbox trace generates way too much data for our needs and c2 audit is not needed so I took the blackbox trace and modiified it with my filters and it runs fine. The issue is I am required to have it running at all times . So accounting for unexpected sql stop and starts I configured for it to start on sql start up.The issue I have is this trace generates files rolling over appending 1,2,4 etc.My concern is when we have an outage the trace will go back to the original file name thus the trace will not start.So trying to avoid renaming files via xp_cmdshell moving etc I thought I could build a string and append a unique value to the end.the problem I am having is getting the @string to execute.I believe this has something to do with the @rtraceid.Here is the original code and below is it modified building a string.If anyone has any suggestions on the best way to ensure a custom server side trace file is unigue everytime it starts please send it my way.-- orginal works fineexec @rc = sp_trace_create @TraceID output, 2, N'E:\tracedata\audit', @maxfilesize, NULL if (@rc != 0) goto error-- this genrates a unique file name but I cannot execute it I get an error.declare @rc intdeclare @TraceID intdeclare @maxfilesize bigintdeclare @newid nvarchar(40)declare @string nvarchar(4000)set @maxfilesize = 50 SET @newid = newid() select @string = 'sp_trace_create @traceid output, 2, N'+''''+'E:\tracedata\pci_audit_'+substring(@newid,1,4) + ',' + (case when @maxfilesize is null then '1' else (cast (@maxfilesize as char(10))) end) + ', NULL ' --if (@rc != 0) goto errorexec @stringMsg 2812, Level 16, State 62, Line 14Could not find stored procedure 'sp_trace_create @traceid output, 2, N'E:\tracedata\pci_audit_888D,50 , NULL '.Thu, 11 Dec 2008 06:52:37 GMT71 camaro