Friday, January 14, 2011

Blog Post: 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:

http://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:
http://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

Georgina Grenville Michelle Trachtenberg Amanda Bynes Ana Ivanovi Jessica Cauffiel

No comments:

Post a Comment