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 "Unabletoreadtracedefinition 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 tracedefinition for SQL Server version 9.0.1187."

Cause

SQL Server Profiler is unableto 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.

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 "Unabletoreadtracedefinition 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:

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 tracedefinition 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 traceto 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.

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 Files\Microsoft SQL Server\100\Tools\Profiler\TraceDefinitions. So, what is the next step ? Do I need to install MSXML ?

Thanks,

Ranga

Vijay Rod

15 Jun 2011 9:30 PM

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".