ASTrace2012 and SQL 2014

May 1, 2014 at 11:08 AM
I'm trying to use ASTrace2012 against a SQL server 2014 installation, but I seem to get a few errors.

Here's the error from ASTraceService.log:

01-05-2014 12:01:13: Exceeded the number of allowed retries for server: .\SQL2014
01-05-2014 12:01:13: Stopping
01-05-2014 12:05:39: Service Started in 'D:\ASTrace'
01-05-2014 12:05:39: Cannot start Analysis Server trace:
01-05-2014 12:05:39: Analysis Server name: '.\SQL2014'
01-05-2014 12:05:39: Trace definition : 'D:\ASTrace\KmnStandard.tdf'
01-05-2014 12:05:39: Error: Failed to initialize object as reader.
01-05-2014 12:05:39: Stack Trace: at Microsoft.SqlServer.Management.Trace.TraceServer.InitializeAsReader(ConnectionInfoBase serverConnInfo, String profileFileName)
at ASTrace.Trace.ConnectOlap(TraceServer& traceServer, String SSASserver) in C:\projects\SSAS Community Samples\ASTrace\CS\ASTrace\Service1.cs:line 124
INNER EXCEPTION: Unable to locate trace definition file for Microsoft Analysis Services version 12.0.0.
Click OK to retrieve it from server. Retrieval may take a few moments.
at ?A0xe4daab47.ProcessError(Int32 hr, UInt16* lpszMessage)
at Microsoft.SqlServer.Management.Trace.CTraceControllerBase.InitSource(Boolean bReOpen)
at Microsoft.SqlServer.Management.Trace.CTraceObjectsRowsetController.InitSource(Boolean bReOpen)
at Microsoft.SqlServer.Management.Trace.TraceServer.InitializeAsReader(ConnectionInfoBase serverConnInfo, String profileFileName)

My first guess is, that it is not possible to use SQL 2014 with this tool yet?
May 1, 2014 at 1:03 PM
I managed to solve this issue myself!

All I had to do was to remove the references to

Microsoft.SQLServer.ConnectionInfo
Microsoft.SQLServer.ConnectionInfoExtended

And then point them to the new version from SQL Server 2014

If anyone is interested in a version running on 2014, just tell me and i'll upload it.

/Kenneth
Marked as answer by DrKermit on 5/1/2014 at 5:56 AM
Coordinator
May 1, 2014 at 4:40 PM
You should also be able to use ASTrace2012 and then edit ASTrace.exe.config and change the two bindingRedirect tags from 11.0.0.0 to 12.0.0.0. When I have time I will test this and publish ASTrace2014.
May 2, 2014 at 12:24 PM
Hi Greg,

I actually tried that first, but with no luck.

Thanks for the reply.

/Kenneth
Nov 3, 2014 at 7:20 PM
I tried both recommendations and still seeing errors in the log file

11/3/2014 11:11:51 AM: Stack Trace: at Microsoft.SqlServer.Management.Trace.TraceServer.InitializeAsReader(ConnectionInfoBase serverConnInfo, String profileFileName)
at ASTrace.Trace.ConnectOlap(TraceServer& traceServer, String SSASserver) in C:\projects\SSAS Community Samples\ASTrace\CS\ASTrace\Service1.cs:line 124
INNER EXCEPTION: Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.SqlServer.Management.Trace.TraceUtils.GetSqlToolsBinaryPath(String appendPath)
at Microsoft.SqlServer.Management.Trace.TraceUtils.CreateInstance(String assemblyRelativePath, String objectClass)
at Microsoft.SqlServer.Management.Trace.TraceServer.InitializeAsReader(ConnectionInfoBase serverConnInfo, String profileFileName)
Nov 3, 2014 at 7:20 PM
If you have something working that's great:) Can you upload it or share it?

Thanks for your help.
Coordinator
Oct 22, 2015 at 8:00 PM
Edited Oct 22, 2015 at 8:01 PM
For what it's worth, I was able to get ASTrace2012 to work on a SQL2014 box with the following redirects in the ASTrace.exe.config file:

<?xml version="1.0"?>
<configuration>
    <configSections>
        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
            <section name="ASTrace.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
        </sectionGroup>
    </configSections>
    <applicationSettings>
        <ASTrace.Properties.Settings>
            <!-- standard settings go here-->
        </ASTrace.Properties.Settings>
    </applicationSettings>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
   <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfo" publicKeyToken="89845dcd8080cc91" />
            <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
            <publisherPolicy apply="no" />
         </dependentAssembly>
      </assemblyBinding>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfoExtended" publicKeyToken="89845dcd8080cc91" />
            <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
            <publisherPolicy apply="no" />
         </dependentAssembly>
      </assemblyBinding>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name="Microsoft.SqlServer.Instapi" publicKeyToken="89845dcd8080cc91" />
            <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
            <publisherPolicy apply="no" />
         </dependentAssembly>
      </assemblyBinding>

   </runtime>
</configuration>
Oct 29, 2015 at 12:01 AM
Thanks for posting. I apologize. I got this working sometime ago and never posted back to the forum.
Dec 2, 2015 at 7:53 PM
And maybe do the same for SQL 2016 as it is right around the corner...not a bad idea to do it now and keep ahead of the curve!
Dec 4, 2015 at 12:38 PM
Hi, changing ASTrace.exe config file doesn't work for me. Does anyone have working solution available for download?
Coordinator
Dec 5, 2015 at 3:18 PM
What's the error? Is the service account user a Windows admin and a SSAS admin?
Dec 8, 2015 at 12:55 PM
Yes. User is a windows admin and is added as SSAS admin. Solution was working for me before we moved from SQL Server 2012 to SQL Server 2014. Even though we moved from SS2012 to SS2014 our operation system is still window server 2008R2 SP1. Not sure if that could be a problem.
Coordinator
Dec 9, 2015 at 10:56 PM
Hmm. What's the error? How do you have your .config file setup?
Coordinator
Dec 9, 2015 at 11:00 PM
One other error I've seen is "Unable to read trace definition file Microsoft Analysis Services TraceDefinition 12.0.4100.xml." I was able to workaround that error by deleting that file from “C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Profiler\TraceDefinitions” daily before I stop and restart ASTrace. (As a best practice so that the ASTraceTable doesn't get too big and so that rows are moved to the History table daily, I restart ASTrace daily during a quiet period in the day.)
Dec 15, 2015 at 2:50 PM
Yeah. I was able to make ASTrace work in the same way. I wanted to write solution here just to see you answer suggesting the same. I get same error while running Profiler with GUI (even on servers without ASTrace installed) so in my case issue seems to lay somewhere else. Maybe you know where ?
Jan 21, 2016 at 4:55 PM
Unable to read trace definition file Microsoft Analysis Services TraceDefinition 12.0.4100.xml and similar errors are easily fixed by editing "Microsoft Analysis Services TraceDefinition 12.0.4100.xml". In this file BUILDNUMBER is by default set to "12" where it should be "4100". Worked for me, no need to delete the file.
May 4, 2016 at 6:18 PM
Having similar troubles getting this to work with SQL 2014. Tried all the fixes posted here....

5/4/2016 12:09:03 PM: Service Started in 'C:\tools\ASTrace2012'
5/4/2016 12:09:03 PM: Cannot start Analysis Server trace:
5/4/2016 12:09:03 PM: Analysis Server name: 'AP44'
5/4/2016 12:09:03 PM: Trace definition : 'C:\tools\ASTrace2012\Standard.tdf'
5/4/2016 12:09:03 PM: Error: Failed to initialize object as reader.
5/4/2016 12:09:03 PM: Stack Trace: at Microsoft.SqlServer.Management.Trace.TraceServer.InitializeAsReader(ConnectionInfoBase serverConnInfo, String profileFileName)
at ASTrace.Trace.ConnectOlap(TraceServer& traceServer, String SSASserver) in C:\projects\SSAS Community Samples\ASTrace\CS\ASTrace\Service1.cs:line 124
INNER EXCEPTION: Unable to locate trace definition file for Microsoft Analysis Services version 13.0.0.
Click OK to retrieve it from server. Retrieval may take a few moments.
at ?A0x8963cb16.ProcessError(Int32 hr, UInt16* lpszMessage)
at Microsoft.SqlServer.Management.Trace.CTraceControllerBase.InitSource(Boolean bReOpen)
at Microsoft.SqlServer.Management.Trace.CTraceObjectsRowsetController.InitSource(Boolean bReOpen)
at Microsoft.SqlServer.Management.Trace.TraceServer.InitializeAsReader(ConnectionInfoBase serverConnInfo, String profileFileName)

I noticed the version of 13 instead of the 12 I have in the config file. Double checked all permissions. My analysis server has the same name as the sql server. Config file looks like....

<?xml version="1.0"?>
<configuration>
<configSections>
    <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <section name="ASTrace.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
    </sectionGroup>
</configSections>
<applicationSettings>
<ASTrace.Properties.Settings>
        <setting name="AnalysisServerNames" serializeAs="String">
            <value>AP44</value>
        </setting>
        <setting name="SQLServer" serializeAs="String">
            <value>AP44</value>
        </setting>
        <setting name="SQLServerDatabase" serializeAs="String">
            <value>TraceDB</value>
        </setting>
        <setting name="TraceTableName" serializeAs="String">
            <value>ASTraceTable</value>
        </setting>
        <setting name="TraceDefinition" serializeAs="String">
            <value>Standard.tdf</value>
        </setting>
        <setting name="PreserveHistory" serializeAs="String">
            <value>True</value>
        </setting>
        <setting name="RestartRetries" serializeAs="String">
            <value>5</value>
        </setting>
        <setting name="RestartDelayMinutes" serializeAs="String">
            <value>2</value>
        </setting>
</ASTrace.Properties.Settings>
</applicationSettings>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
     <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfo" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
        <publisherPolicy apply="no" />
     </dependentAssembly>
  </assemblyBinding>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
     <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.ConnectionInfoExtended" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
        <publisherPolicy apply="no" />
     </dependentAssembly>
  </assemblyBinding>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
     <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Instapi" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
        <publisherPolicy apply="no" />
     </dependentAssembly>
  </assemblyBinding>
</runtime>
</configuration>
Coordinator
May 7, 2016 at 2:15 PM
Ted do you have AS2014 or AS2016? I see version 13.0 mentioned in the error which makes me suspicious the SSAS server is 2016 version.
May 9, 2016 at 10:43 PM
Oh yep, I guess that's the problem isn't it? Any plans for a 2016 upgrade at any point?
Coordinator
May 10, 2016 at 6:58 PM
I haven't tested it, but in the bindingRedirect you should be able to redirect version 10 to version 13 and it should work with SSAS 2016. If you try that, please let us know.