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."
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.
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:
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
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
Support Engineer ,Microsoft SQL Server Support
Reviewed by Balmukund Lakhani
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 ?
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.
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?
Nice article ..