Unable to read trace definition file Microsoft SQL Server TraceDefinition n.n1.n2.xml

  • [アーティクル]

Symptoms

When you open SQL Server profiler, and try to start a new  trace (or when try to open an existing trace), you get the error "Unable to read trace definition file Microsoft SQL Server TraceDefinition 9.0.1187.xml. Click OK to retreive it from server. Retreival may take a few moments".

You click OK, and get this message:

"Cannot retrieve trace definition for SQL Server version 9.0.1187."

Cause

SQL Server Profiler is unable to locate the definition file. In this case, the file name is "Microsoft SQL Server TraceDefinition 9.0.1187.xml".

However this name can be different depending on version of Profiler used to capture the trace file (in case we get error when opening trace file) so please ensure you note the file name in this error.

Resolution

There are multiple reasons for this error and below are some possible steps:

1.a. Ensure the file is present in the TraceDefinitions directory for the appropriate version of SQL Server Profiler. Below is the default path for this directory. In case mentioned above, "Microsoft SQL Server TraceDefinition 9.0.1187.xml" was present in the TraceDefinitions directory so we would need to continue with remaining steps.

--on 64-bit systems
C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Profiler\TraceDefinitions

--on 32-bit  systems
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\TraceDefinitions

In another case, my colleague used Profiler of SQL Server 2008 R2 to capture a SQL Server trace. Another colleague was try to open this trace in Profiler of SQL Server 2005 but was not able to open it due to similar error on a different file "Unable to read trace definition file Microsoft SQL Server TraceDefinition 10.50.0.xml" (note the 10.50 in the file name which denotes 2008 R2).
We checked the C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Profiler\TraceDefinitions (we checked the "90" folder since the Profiler that's displaying the error is SQL Server 2005 which internally is denoted by "9.0") on this 64-bit system and did not find file 'Microsoft SQL Server TraceDefinition 10.50.0.xml' so we copied this file from a working system to this system and re-opened Profiler which now worked fine.

1.b.   This issue may also occur if the TraceDefinition xml file mentioned above is present but a required file like ProfilerSpecialEvents.XML.profiler is not present in the same directory. Hence please consider comparing the files in the TraceDefinitions directory to the files in a working/new installation of the same SQL Server version and copying any missing files:

--on 64-bit systems
C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Profiler\TraceDefinitions

--on 32-bit  systems
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\TraceDefinitions

For example, in another incident, we were using Profiler of SQL Server 2008 R2 to connect to a SQL 2005 express instance and got error “Cannot retrieve trace definition for SQL Server version 9.0.4053” when trying to create a new trace. In this case, ‘Microsoft SQL Server TraceDefinition 9.0.0.xml’ was already present in the TraceDefinitions directory but file ‘ProfilerSpecialEvents.XML.profiler’ was not present. So we copied file ‘ProfilerSpecialEvents.XML.profiler’ from a working SQL Server 2008 R2 instance’s TraceDefinition directory and this time we were able to create a new trace to the SQL 2005 instance without any errors.

2. Ensure the "SQLPath" registry key denotes the correct value:

a. Go to start>run, type regedit and hit OK
b. Locate the key mentioned below.
c. Verify/Change the SQLPath key value to point to place where the tools are installed.

--On 64 bit machine its under below hive.

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup

Assuming default setup, it should ideally point to C:\Program Files (x86)\Microsoft SQL Server\90\Tools

--On 32 bit machine its below

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup

Assuming default setup, it should ideally point to C:\Program Files\Microsoft SQL Server\90\Tools

PS Above is for SQL Server 2005. For SQL Server 2008 and for SQL Server 2008 R2, just change the "90" mentioned above to "100" for both the registry key and for the path to point.

3. Install/Re-install MSXML and run "regsvr32 msxml6.dll" in command prompt:

https://www.microsoft.com/downloads/en/details.aspx?FamilyID=993c0bcf-3bcf-4009-be21-27e85e1857b1&DisplayLang=en
Microsoft Core XML Services (MSXML) 6.0
--this is also available in the Feature Pack for this version of SQL Server

More Information

SQL Server release names and versions:
SQL Server 2000 = 8.0
SQL Server 2005 = 9.0
SQL Server 2008 = 10.0
SQL Server 2008 R2 = 10.50

Reference:
https://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e61b4800-bd69-4819-955a-e1c53594e1ef/

Vijay Rodrigues

Support Engineer ,Microsoft SQL Server Support

Reviewed by

Balmukund Lakhani

Comments

  • Anonymous
    June 15, 2011
    I have the same error when trying to open a sql trace from sql 2008 R2 on a sql 2008 server. I copied the Microsoft SQL Server TraceDefinition 10.50.1600.xml, also ProfilerSpecialEvents.XML.profiler already exists in the folder C:Program FilesMicrosoft SQL Server100ToolsProfilerTraceDefinitions. So, what is the next step ? Do I need to install MSXML ? Thanks, Ranga

  • Anonymous
    June 15, 2011
    hi Ranga, ProfilerSpecialEvents.XML.profiler is just an example. Step 1.b. is for ALL files - "please consider comparing the files in the TraceDefinitions directory". Yes, please consider MSXML reinstall step 3, after step 1.b. and step 2. Vijay Rod.

  • Anonymous
    June 28, 2012
    I am experiencing the same problem, and it is something to do with the 64-vs-32 paths On my 64-bit machine, the %ProgramFiles% environment variable points to c:Program Files but the XML file this is looking for is stored in the 32 bit path, C:Prgram files(x86) Why does this software look down the 64-bit path after it was installed in the 32 bit path?

  • Anonymous
    August 21, 2012
    Nice article ..

  • Anonymous
    December 03, 2014
    These two location references should surely be the opposite way around, which may be why Michael was querying this (alas with no response)?: --on 64-bit systems <-- SHOULD READ 32-bit C:Program Files (x86)Microsoft SQL Server90ToolsProfilerTraceDefinitions --on 32-bit  systems <-- SHOULD READ 64-bit C:Program FilesMicrosoft SQL Server90ToolsProfilerTraceDefinitions By convention, x86 always refers to 32 bit hardware architecture. x64 (and occasionally x86-64) always refers to 64 bit hardware.