Error running ClearTrace

gpollokoff · July 12, 2016, 3:27pm

I can't seem to get past this error:

Files to process: 1 ( 19.8 MB )
Clearing Target Tables...
SMO Version: Microsoft.SqlServer.ConnectionInfoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Processing: ClearTraceTest.trc

Trace File Read Error in C:\DBA\Temp\ClearTraceTest.trc (Exception has been thrown by the target of an invocation.)
Inner Exception: Exception has been thrown by the target of an invocation.
Inner Exception: Failed to initialize object as reader.
Inner Exception: Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
You can visit the support forum at http://forums.sqlteam.com/c/cleartrace
Done.

graz · July 12, 2016, 4:02pm

And you have the full client tools installed?

graz · July 12, 2016, 4:05pm

Also, can you run the following in PowerShell and see post what it returns?

cls
$PSVersionTable.PSVersion
[reflection.assembly]::loadwithpartialname("Microsoft.SqlServer.Instapi")
[reflection.assembly]::Load("Microsoft.SqlServer.Instapi, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");

gpollokoff · July 12, 2016, 4:44pm

Major Minor Build Revision

5 0 10586 117

CodeBase : file:///C:/windows/assembly/GAC_MSIL/Microsoft.SqlServer.Instapi/12.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Instapi.dll
FullName : Microsoft.SqlServer.Instapi, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
EntryPoint :
DefinedTypes : {AssemblyVersionInfo, Microsoft.SqlServer.InstAPIException, Microsoft.SqlServer.InstAPI, Microsoft.SqlServer.SQL_SVCS...}
Evidence : {<System.Security.Policy.GacInstalled version="1"/>
, <System.Security.Policy.Hash version="2">

</System.Security.Policy.Hash>
,
, <System.Security.Policy.Url version="1">
file:///C:/windows/assembly/GAC_MSIL/Microsoft.SqlServer.Instapi/12.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Instapi.dll
</System.Security.Policy.Url>
...}
PermissionSet : {}
SecurityRuleSet : Level1
ManifestModule : Microsoft.SqlServer.Instapi.dll
ReflectionOnly : False
Location : C:\windows\assembly\GAC_MSIL\Microsoft.SqlServer.Instapi\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Instapi.dll
ImageRuntimeVersion : v2.0.50727
GlobalAssemblyCache : True
HostContext : 0
IsDynamic : False
EscapedCodeBase : file:///C:/windows/assembly/GAC_MSIL/Microsoft.SqlServer.Instapi/12.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Instapi.dll
ExportedTypes : {Microsoft.SqlServer.InstAPIException, Microsoft.SqlServer.InstAPI, Microsoft.SqlServer.SQL_SVCS, Microsoft.SqlServer.INST_ID...}
IsFullyTrusted : True
CustomAttributes : {[System.Reflection.AssemblyCopyrightAttribute("Microsoft Corp. All rights reserved.")], [System.Reflection.AssemblyTrademarkAttribute("Microsoft SQL
Server is a registered trademark of Microsoft Corporation.")], [System.Runtime.CompilerServices.RuntimeCompatibilityAttribute(WrapNonExceptionThrows =
True)], [System.Runtime.InteropServices.ComVisibleAttribute((Boolean)True)]...}
Modules : {Microsoft.SqlServer.Instapi.dll}

Exception calling "Load" with "1" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=13.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."
At line:4 char:1

[reflection.assembly]::Load("Microsoft.SqlServer.Instapi, Version=13. ...

  + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
  + FullyQualifiedErrorId : FileNotFoundException

graz · July 12, 2016, 8:31pm

My best guess is that the SQL Server 2016 Engine is installed but the SSMS 2016 tools aren't. In SQL Server 2016 they're a separate install. Can you confirm whether SSMS 2016 is installed?

A second option is to replace the contents of ClearTrace.EXE.config with the following.

<?xml version="1.0"?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings/>
  <startup  useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>




  <!--
  
  Uncommnet the following section to remap the SMO vesions
  This is built with SQL Server 2012 SMO.  It should run
  with most common SMO vesions.
  
  Replace the "newVersion" with the version you'd like 
  to target.
  
  SQL Server 2008     10.0.0.0
  SQL Server 2008 R2  10.0.0.0  (Uses the previous SMO)
  SQL Server 2012     11.0.0.0
  SQL Server 2014     12.0.0.0
  SQL Server 2016     13.0.0.0
  
  -->
  
  
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Smo"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
         Assembly versions can be redirected in application, 
          publisher policy, or machine configuration files. 
        <bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>

      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfo"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
         Assembly versions can be redirected in application, 
          publisher policy, or machine configuration files. 
        <bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>


      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfoExtended"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
         Assembly versions can be redirected in application, 
          publisher policy, or machine configuration files. 
        <bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>

      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Management.Sdk.Sfc"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
         Assembly versions can be redirected in application, 
          publisher policy, or machine configuration files. 
        <bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>

      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.SmoExtended"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
         Assembly versions can be redirected in application, 
          publisher policy, or machine configuration files. 
        <bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>


      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.SqlEnum"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
         Assembly versions can be redirected in application, 
          publisher policy, or machine configuration files. 
        <bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>

    </assemblyBinding>
  </runtime>
  
</configuration>

edwillis · January 19, 2017, 8:09pm

I am getting the same error. Have SQL Server 2016 and SSMS installed. Tried adding the from 12.0.0.0 to 13 but still get the error?

Files to process: 8 ( 3,687.1 MB )
Starting at: 1/19/2017 3:07:51 PM
Clearing Target Tables...

Processing: Untitled - 1.trc

Trace File Read Error in C:\Downloads\DBBackups\Untitled - 1.trc (Exception has been thrown by the target of an invocation.)
Inner Exception: Exception has been thrown by the target of an invocation.
Inner Exception: Failed to initialize object as reader.
Inner Exception: Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
You can visit the support forum at http://forums.sqlteam.com/c/cleartrace
Done.

Here is the config file maybe something is wrong or missing?

<?xml version="1.0"?>

  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.Smo"
      publicKeyToken="89845dcd8080cc91"
      culture="neutral" />
     Assembly versions can be redirected in application, 
      publisher policy, or machine configuration files. 
    <bindingRedirect oldVersion="12.0.0.0" newVersion="13.0.0.0" />
  </dependentAssembly>

  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfo"
      publicKeyToken="89845dcd8080cc91"
      culture="neutral" />
     Assembly versions can be redirected in application, 
      publisher policy, or machine configuration files. 
    <bindingRedirect oldVersion="12.0.0.0" newVersion="13.0.0.0" />
  </dependentAssembly>


  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfoExtended"
      publicKeyToken="89845dcd8080cc91"
      culture="neutral" />
     Assembly versions can be redirected in application, 
      publisher policy, or machine configuration files. 
    <bindingRedirect oldVersion="12.0.0.0" newVersion="13.0.0.0" />
  </dependentAssembly>

  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.Management.Sdk.Sfc"
      publicKeyToken="89845dcd8080cc91"
      culture="neutral" />
     Assembly versions can be redirected in application, 
      publisher policy, or machine configuration files. 
    <bindingRedirect oldVersion="12.0.0.0" newVersion="13.0.0.0" />
  </dependentAssembly>

  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.SmoExtended"
      publicKeyToken="89845dcd8080cc91"
      culture="neutral" />
     Assembly versions can be redirected in application, 
      publisher policy, or machine configuration files. 
    <bindingRedirect oldVersion="12.0.0.0" newVersion="13.0.0.0" />
  </dependentAssembly>


  <dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.SqlEnum"
      publicKeyToken="89845dcd8080cc91"
      culture="neutral" />
     Assembly versions can be redirected in application, 
      publisher policy, or machine configuration files. 
    <bindingRedirect oldVersion="12.0.0.0" newVersion="13.0.0.0" />
  </dependentAssembly>
</assemblyBinding>

graz · January 20, 2017, 4:14am

Well bummer. Let me do some more testing. I'm not sure exactly what's going on though.

ScottMay · July 30, 2020, 9:03pm

Did this ever get resolved? I'm running into the same issue.
Windows Server 2019
SQL Server 2016 SP2
SSMS 18

"Inner Exception: Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\pfclnt.dll' or one of its dependencies. The system cannot find the file specified."

graz · July 31, 2020, 11:20am

Try installing the "150" version of SMO and see if that works: https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/150.18208.0

The later versions don't support reading trace files.

Var · September 19, 2020, 4:12am

Hello.

Please could you check the following error I am getting when trying to import a sql server 2012 trace file? In case it helps: I am using Windows 10 and I have SQL Server 2012, 2014, 2016 and 2019 installed with latest SSMS.

Files to process: 1 ( 110 KB )
Clearing Target Tables...
SMO Version: Microsoft.SqlServer.ConnectionInfoExtended, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Processing: test.trc

Trace File Read Error in C:\Temp\test.trc (Exception has been thrown by the target of an invocation.)
Inner Exception: Exception has been thrown by the target of an invocation.
Inner Exception: Failed to initialize object as reader.
Inner Exception: Failed to get SQL Tools directory path from InstAPI.
You can visit the support forum at ...
Done.

I have used the tool a few years ago and I loved it!
Unfortunately I can no longer use it in my new environment.

Thank you very much.

graz · September 20, 2020, 3:33pm

Do you know what SMO versions you have installed?

Var · September 23, 2020, 6:52am

Hello.

Not sure if this is the way to check, but the following PS code 'dir C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo' returns this:

11.0.0.0__89845dcd8080cc91
12.0.0.0__89845dcd8080cc91
13.0.0.0__89845dcd8080cc91

Please let me know if you need more info.

Thank you very much.

graz · September 24, 2020, 5:39pm

Ok, let's try another thing. Do you know what versions of SSMS you have installed?

Var · September 25, 2020, 12:45am

It's the latest - v18.6.

graz · September 29, 2020, 3:07pm

So this is a challenging problem to solve and I don't know that I can. Just to be clear, the latest versions of SSMS and SMO don't support using SMO to read trace files. Which means I have no API to read trace files. And Microsoft hasn't published the file layouts. So here are some best guesses on what might work.

The best solution is to switch to extended events. Fully supported and works well.

The next best solution is to build a VM with SQL Server 2016, it's related SSMS, and run ClearTrace on that. The SMO libraries used some DLL buried deep inside SQL Server or SSMS to read the trace files. That doesn't seem to be present in the latest builds.

You may be able to make it work by installing an earlier version of SSMS.

You can try to hard code the SMO version in the ClearTrace.exe.config like this. This may remap the version of SSMS it is trying to use. I've had little success with this. I'm not spending much time trying to make this work since Trace is going away.

Good luck!

<?xml version="1.0"?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings/>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>


  <!--
  
  Use the following section to remap the SMO vesions
  This is built with SQL Server 2017 SMO.  It should run
  with most common SMO vesions.
  
  Replace the "newVersion" with the version you'd like 
  to target.
  
  SQL Server 2012     11.0.0.0
  SQL Server 2014     12.0.0.0
  SQL Server 2016     13.0.0.0
  SQL Server 2017     14.0.0.0 (Or just comment out the runtime section)
  ...
  
  -->

  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfoExtended"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
        <bindingRedirect oldVersion="14.0.0.0" newVersion="13.0.0.0" />
      </dependentAssembly>

      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.XEvent.Linq"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
        <bindingRedirect oldVersion="14.0.0.0" newVersion="13.0.0.0" />
      </dependentAssembly>

      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.XE.Core"
          publicKeyToken="89845dcd8080cc91"
          culture="neutral" />
        <bindingRedirect oldVersion="14.0.0.0" newVersion="13.0.0.0" />
      </dependentAssembly>
    
    </assemblyBinding>
  </runtime>
</configuration>