Profiler - Logging Results to a Table

Question

Post reply

Profiler - Logging Results to a Table

Viewing 3 posts - 16 through 17 (of 17 total)

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

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 1

If you want to trace in batch, you need to trace to a file !

Afterward you can load the file to a given (empty) table using :

-- HOW TO: Programmatically Load Trace Files into Tables

-- http://support.microsoft.com/kb/270599

--

SELECT *

INTO myserver_mydb_CLOR_070322

FROM ::fn_trace_gettable('F:\myTraces\trace_myserver_mydb_070322.trc', 32)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Krop Newbie Points: 7 More actions · Answer 2

When you create the trace, don't add ".trc' to the end of the name as sp_trace_create will also add it.

exec sp_trace_create @traceid = @traceid OUTPUT,

@options = 0,

@tracefile = 'C:\Temp\myTraceFile', -- DO NOT ADD .TRC HERE!

@maxfilesize = 10,

@stoptime = 0

SELECT * FROM ::fn_trace_gettable('C:\Temp\myTraceFile.trc', default)

If you have already added it, just amend the filename from "myTraceFile.trc.trc" to "myTraceFile.trc" or you'll only be able to open it using the Profiler GUI and not using fn_trace_gettable.

Rgds,

Krop

Ranga N SSChasing Mays Points: 642 More actions · Answer 3

Dinesh Priyankara (7/24/2003)
<font face='Verdana'>
Ok. What I want is : create a trace using sp_trace_create with option 2. That's what I have done. But I cannot read the tracefile untill I stop and restart the server. I am getting an error when I call fn_trace_gettable function.
<b>
SELECT * FROM :: fn_trace_gettable (N'E:\Traces\monitor.trc', 1)
Output:
Server: Msg 567, Level 16, State 2, Line 1
File 'E:\Traces\monitor.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.[/b]
As per microsoft site http://support.microsoft.com/support/kb/articles/q273/9/72.asp , This is bug. And they have given a workaround but no luck.
I highly appreciate if you can help me on this.
Dinesh
</font id='Verdana'>
MCP MCSE MCSD MCDBA

The fn_trace_gettable function looks for the trace file N'E:\Traces\monitor.trc' on the sql server you are connected in the management studio. For example if the trace file E:\Traces\monitor.trc exists on the SQL Server "SQLSERVerTest", connect to this SQL server thru mgmt studio and then try to query.