Re: Profiler Bug viewing trace flat file?

One other tip. Make sure you have your app schema qualify the stored procedure calls. Instead of:

exec PostAvailableContractsCache_sp

it should be

exec dbo.PostAvailableContractsCache_sp

or what ever your schema happens to be.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message news:2E7D2631-FCBC-4B42-85EB-8006425EEAEB@xxxxxxxxxxxxxxxx
The Application Name filter is correct. I obfuscated it intentionally -
sorry. I need the filter as these servers are very busy at 300+ batch
requests/sec.

The TextData column is definitely populated when I query resulting trace
file through fn_trace_gettable. Here are some sample rows:

select [TextData]
from
fn_trace_gettable('g:\mssql\trace\archive\Logger_GUI_investigation_XXXXXXX_20080201.trc',0)

TextData
----------------------------------------------------------------------------------
exec PostAvailableContractsCache_sp 'CONNECT\fuuser404','31 January 2008
09:35:26'
exec PostAvailableContractsCache_sp 'CONNECT\fuuser304','31 January 2008
10:04:26'
.
.

All servers are SQL2005 SP2+.

I only want the RPC:Completed event. This works fine when the event is
selected in Profiler and run from Profiler. If I then export the trace
definition from Profiler, run the T-SQL trace, stop it, and then open the
resulting trace file in Profiler, the TextData field is not there. But its
there to view through fn_trace_gettable.

I'm not certain that SSMS will generate RPC events as a test.

As I said its not an issue for us as we load the trace files into tables but
it does expose a bug somewhere in the Profiler GUI code.

Thanks for the tip on sys.traces!
--
-- cranfield, DBA


"Kalen Delaney" wrote:

I had some problems with this script. First, you have a filter to only
return events when the Application Name is 'myfilter', and I don't have an
application of that name. Even when I comment out that filter, I am still
getting all NULLs in the textdata column.

I noticed that the only event you are capturing is RPC:Completed. Are you
sure you are actually seeing the TextData column populated? Running a test
through SSMS will generate RPC:Completed events so I see nothing there. If I
had Batch:Completed to the list of events, with the text data column:

exec sp_trace_setevent @TraceID, 12, 1, @on

Then I can see text data just fine, both in the output from
fn_trace_gettable and the Profiler.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
news:630D3BC9-F968-402C-AEF8-3C48A5BE1E03@xxxxxxxxxxxxxxxx
> I'm collecting from 30 servers and they're all the same. Its not a > major
> problem as all the trace files get loaded into SQL through
> fn_trace_gettable
> and there we get the TextData. I do open the odd one up through > profiler
> though and this is where I notice the problem.
>
> I guess you are correct in that fn_trace_gettable may derive the > Textdata
> from the binary data.
>
> Here is my code. Try and you will see:
>
> -- Create a Queue
> declare @rc int
> declare @TraceID int
> declare @maxfilesize bigint
> set @maxfilesize = 5
>
> exec @rc = sp_trace_create @TraceID output, 0, N'c:\mytrace',
> @maxfilesize,
> NULL
> if (@rc != 0) goto error
>
> -- Set the events
> declare @on bit
> set @on = 1
> exec sp_trace_setevent @TraceID, 10, 15, @on
> exec sp_trace_setevent @TraceID, 10, 16, @on
> exec sp_trace_setevent @TraceID, 10, 1, @on
> exec sp_trace_setevent @TraceID, 10, 9, @on
> exec sp_trace_setevent @TraceID, 10, 17, @on
> exec sp_trace_setevent @TraceID, 10, 10, @on
> exec sp_trace_setevent @TraceID, 10, 18, @on
> exec sp_trace_setevent @TraceID, 10, 11, @on
> exec sp_trace_setevent @TraceID, 10, 12, @on
> exec sp_trace_setevent @TraceID, 10, 13, @on
> exec sp_trace_setevent @TraceID, 10, 6, @on
> exec sp_trace_setevent @TraceID, 10, 14, @on
>
>
> -- Set the Filters
> declare @intfilter int
> declare @bigintfilter bigint
>
> exec sp_trace_setfilter @TraceID, 10, 1, 6, N'myfilter'
> exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler -
> 7412f6af-1549-44c6-bc15-4d0e9dbc9d61'
> -- Set the trace status to start
> exec sp_trace_setstatus @TraceID, 1
>
> -- display trace id for future references
> select TraceID=@TraceID
> goto finish
>
> error:
> select ErrorCode=@rc
>
> finish:
> go
>
>
> -- stop the trace
> declare @trace_id INT
> select @trace_id = 0
>
> select @trace_id = traceid
> from fn_trace_getinfo (NULL)
> where value = 'c:\mytrace.trc'
>
> IF @trace_id <> 0
> BEGIN
> EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0
> EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2
> END
>
>
>
> -- > -- cranfield, DBA
>
>
> "Andrew J. Kelly" wrote:
>
>> That function will return all the columns regardless of if they were >> in
>> the
>> original trace or not. Most will simply be null though. It may be that
>> the
>> function is getting the data from the Binary column to display in the
>> textdata but this sounds really weird since you say that Textdata is >> in
>> the
>> trace definition. Are you on the latest service pack? Have you tried
>> creating a new trace definition and file from scratch to see if it is
>> just
>> this one file?
>>
>> -- >> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
>> news:D9A75649-5AD5-4A55-9B1A-10A668B7A3B1@xxxxxxxxxxxxxxxx
>> > Hi Kalen
>> >
>> > No, the TextData column is not in the properties in Profiler.
>> >
>> > When I select from the file using fn_trace_gettable, TextData is the
>> > first
>> > column returned and has the data I require.
>> >
>> > -- >> > -- cranfield, DBA
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Can you look in the trace properties once you open it in Profiler >> >> to
>> >> see
>> >> if
>> >> text data is there?
>> >>
>> >> -- >> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.InsideSQLServer.com
>> >> http://blog.kalendelaney.com
>> >>
>> >>
>> >> "Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
>> >> news:4FC6DE47-D82F-4933-A9FE-EF34F01A3ACD@xxxxxxxxxxxxxxxx
>> >> > Hello
>> >> >
>> >> > I have recorded a Profiler trace to a flat file using the >> >> > sp_trace
>> >> > procs.
>> >> >
>> >> > When I query the flat file using the fn_trace_gettable i can see >> >> > the
>> >> > TextData field but when I open the flat file through the Profiler
>> >> > GUI I
>> >> > dont
>> >> > see the TextData field.
>> >> >
>> >> > These are the events I'm logging:
>> >> >
>> >> > -- Set the events
>> >> > declare @on bit
>> >> > set @on = 1
>> >> > exec sp_trace_setevent @TraceID, 10, 15, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 16, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 1, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 9, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 17, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 2, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 10, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 18, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 11, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 12, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 13, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 6, @on
>> >> > exec sp_trace_setevent @TraceID, 10, 14, @on
>> >> >
>> >> > is this a BUG or is there a way to see the Textdata field through
>> >> > Profiler?
>> >> >
>> >> > tks
>> >> > -- >> >> > -- cranfield, DBA
>> >>
>> >>
>> >>
>>
>>




.

Relevant Pages

  • Re: Profiler Bug viewing trace flat file?
    ... The TextData column is definitely populated when I query resulting trace ... selected in Profiler and run from Profiler. ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler Bug viewing trace flat file?
    ... sure you are actually seeing the TextData column populated? ... problem as all the trace files get loaded into SQL through ... I do open the odd one up through profiler ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler Bug viewing trace flat file?
    ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata field ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler Bug viewing trace flat file?
    ... It may be that the function is getting the data from the Binary column to display in the textdata but this sounds really weird since you say that Textdata is in the trace definition. ... the TextData column is not in the properties in Profiler. ... > I have recorded a Profiler trace to a flat file using the sp_trace> procs. ...
    (microsoft.public.sqlserver.tools)
  • Re: How do I identify a command in a job step?
    ... TextData values along the lines of: ... exec msdb..sp_get_dtspackage N'YourPackage', null, null ... >in there that indicates which package is being run. ... Run a trace or Profiler ...
    (microsoft.public.sqlserver.server)