INF: How to Use the SQL Server 7.0 TraceReader Objects (273933)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q273933

SUMMARY

The Microsoft BackOffice Resource Kit version 4.5 includes a COM object that you can use to programmatically access a SQL Server 7.0 Profiler trace file (.trc). This article provides instructions and a sample Microsoft Visual Basic application that demonstrates how to use the COM TraceReader.

MORE INFORMATION

NOTE: The software supplied with the BackOffice Resource Kit is not officially supported.

How to Install and Register the COM Trace Reader

  1. The files that you need are located in the SQL\TracRead folder of the BackOffice Resource Kit version 4.5. Copy the Readtrc.dll and Readtrcps.dll files to the system directory.
  2. Run this command to register the TraceReader object:

    Regsvr32 Readtrc.dll
  3. Create a new Visual Basic project.
  4. On the Project menu, click References and add a reference to "Microsoft SQL Server Trace Files (7.0 Prototype)".
  5. Add a new .bas module to the project, and then paste the code that is provided under the Code Sample topic into the module.

    NOTE: The routine AnalyzeTraceFile steps through a Profiler .trc file and looks for queries that run for longer than 30 seconds. The output goes to the debug window (Debug.Print). The SQLTraceEvent object exposes a property for each of the columns in SQL Server 7.0 Profiler. The information that is available through the SQLTraceEvent object may allow you to extend the sample to identify more complex patterns.

    Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

    Code Sample

    Option Explicit

'Trace events
Global Const EVENT_TRACESTART = 0
Global Const EVENT_TRACEPAUSE = 1
Global Const EVENT_TRACEAUTOPAUSE = 3
Global Const EVENT_TRACEAUTORESTART = 4
Global Const EVENT_TRACESTOP = 5
Global Const EVENT_EVENTREQUIRED = 6
Global Const EVENT_FILTERCHANGED = 7
Global Const EVENT_UNDEFINED = 8
Global Const EVENT_REPLAYERROR = 62
Global Const EVENT_REPLAYINTERNALERROR = 63
Global Const EVENT_REPLAYRESULTSET = 64
Global Const EVENT_REPLAYRESULTROW = 65
'TSQL events
Global Const EVENT_RPCCOMPLETED = 10
Global Const EVENT_RPCSTARTING = 11
Global Const EVENT_SQLBATCHCOMPLETED = 12
Global Const EVENT_SQLBATCHSTARTING = 13
Global Const EVENT_SQLSTMTSTARTING = 40
Global Const EVENT_SQLSTMTCOMPLETED = 41
'Sessions events
Global Const EVENT_CONNECT = 14
Global Const EVENT_DISCONNECT = 15
Global Const EVENT_EXISTINGCONNECTION = 17
'Misc events
Global Const EVENT_ATTENTION = 16
Global Const EVENT_SERVICECONTROL = 18
Global Const EVENT_SERVERMEMORYCHANGE = 81
Global Const EVENT_LOGINFAILED = 20
Global Const EVENT_AUTOUPDATESTATS = 58
Global Const EVENT_EXECUTIONPLAN = 68
Global Const EVENT_PREPARESQL = 71
Global Const EVENT_EXECPREPAREDSQL = 72
Global Const EVENT_UNPREPARESQL = 73
'Transactions events
Global Const EVENT_DTCTRANSACTION = 19
Global Const EVENT_SQLTRANSACTION = 50
Global Const EVENT_TRANSACTIONLOG = 54
'Errors and warnings events
Global Const EVENT_EVENTLOG = 21
Global Const EVENT_ERRORLOG = 22
Global Const EVENT_EXCEPTION = 33
Global Const EVENT_HASHWARNING = 55
Global Const EVENT_OLEDBERRORS = 61
Global Const EVENT_EXECUTIONWARNINGS = 67
Global Const EVENT_SORTWARNING = 69
Global Const EVENT_MISSINGCOLUMNSTATISTICS = 79
Global Const EVENT_MISSINGJOINPREDICATE = 80
'Locks events
Global Const EVENT_LOCKRELEASED = 23
Global Const EVENT_LOCKACQUIRED = 24
Global Const EVENT_LOCKDEADLOCK = 25
Global Const EVENT_LOCKDEADLOCKCHAIN = 59
Global Const EVENT_LOCKCANCEL = 26
Global Const EVENT_LOCKTIMEOUT = 27
Global Const EVENT_LOCKESCALATION = 60
'SQL Operators events
Global Const EVENT_INSERT = 28
Global Const EVENT_UPDATE = 29
Global Const EVENT_DELETE = 30
Global Const EVENT_SELECT = 31
'? events
Global Const EVENT_CONNECTIONBEINGKILLED = 32
'Stored Procedures events
Global Const EVENT_SPCACHEMISS = 34
Global Const EVENT_SPCACHEINSERT = 35
Global Const EVENT_SPCACHEREMOTE = 36
Global Const EVENT_SPRECOMPILE = 37
Global Const EVENT_SPCACHEHIT = 38
Global Const EVENT_SPEXECCONTEXTHIT = 39
Global Const EVENT_SPSTARTING = 42
Global Const EVENT_SPCOMPLETED = 43
Global Const EVENT_SPSTMTSTARTING = 44
Global Const EVENT_SPSTMTCOMPLETED = 45
'Objects events
Global Const EVENT_OBJECTCREATED = 46
Global Const EVENT_OBJECTDELETED = 47
Global Const EVENT_OBJECTOPENED = 48
Global Const EVENT_OBJECTCLOSED = 49
'Scans events
Global Const EVENT_SCANSTARTED = 51
Global Const EVENT_SCANSTOPPED = 52
'Cursors events
Global Const EVENT_CURSOROPEN = 53
Global Const EVENT_CURSORPREPARE = 70
Global Const EVENT_CURSOREXECUTE = 74
Global Const EVENT_CURSORRECOMPILE = 75
Global Const EVENT_CURSORIMPLICITCONVERSION = 76
Global Const EVENT_CURSORUNPREPARE = 77
Global Const EVENT_CURSORCLOSE = 78
'User configurable events
Global Const EVENT_USERCONFIGURABLE1 = 82
Global Const EVENT_USERCONFIGURABLE2 = 83
Global Const EVENT_USERCONFIGURABLE3 = 84
Global Const EVENT_USERCONFIGURABLE4 = 85
Global Const EVENT_USERCONFIGURABLE5 = 86

Sub AnalyzeTraceFile(TraceFile As String)
  Dim f As READTRCLib.SQLTraceFileReader
  Dim e As READTRCLib.SQLTraceEvent
  
  Set f = New READTRCLib.SQLTraceFileReader
  ' Open the trace file
  f.FileName = TraceFile
  
  ' For each event in the file
  For Each e In f
    Select Case e.EventClass
      Case EVENT_SQLBATCHCOMPLETED, EVENT_RPCCOMPLETED
        If e.Duration > 30000 Then
          Debug.Print "Long-running query (" & e.Duration & "ms): " _
            & e.Text
        End If
    End Select 
    DoEvents
  Next e
End Sub
In your code, call AnalyzeTraceFile with the name of the .trc file to invoke the routine. For example: 
      ...
      Call AnalyzeTraceFile("C:\Sqltrace.trc")
      ...

Other Approaches to Analyze Profiler .trc Files

For many types of troubleshooting, the filtering and ordering capabilities that are built into the Profiler GUI are sufficient. For cases that require more complex analysis, open the .trc file in the GUI, and then save the trace to a trace table.

There is no COM Trace Reader object for Microsoft SQL Server 2000. However, you can programmatically access the contents of a SQL Server 2000 Profiler trace file without having to first save it to a trace table with the fn_trace_gettable Transact-SQL built-in function.

REFERENCES

For more information, see SQL Server 2000 Books Online.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbinfo KB273933
©2004 Microsoft Corporation. All rights reserved.