Today I needed to process some results files from Distributed Replay to compare before and after metrics from a synchronized replay that I had a client run for testing a change in hardware.  I’ve done this before using PowerShell to automate things and the script for it is pretty simple:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfoExtended") | Out-Null;
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null;
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

$InputFile = "E:\Results\27\ResultDir\ReplayResult.trc";
$TableName = "ReplayResults_2"

$conn = New-Object "Microsoft.SqlServer.Management.Common.SqlConnectionInfo";
$conn.ServerName = ".";
$conn.DatabaseName = "ReplayAnalysis"
$InputTrace = New-Object "Microsoft.SqlServer.Management.Trace.Tracefile";
$InputTrace.InitializeAsReader($InputFile);
$TableWriter = New-Object "Microsoft.SqlServer.Management.Trace.TraceTable";
$TableWriter.InitializeAsWriter($InputTrace, $conn, $TableName);

while($TableWriter.Write())
{}

However, when I tried to do this on a new VM that was running Windows Server 2012R2 with SQL Server 2016 using the PowerShell ISE I kept running into the following exception that I had never encountered in the past before:

Exception calling “InitializeAsReader” with “1” argument(s): “Failed to initialize object as reader.”
At line:14 char:1
+ $InputTrace.InitializeAsReader($InputFile);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlTraceException

I asked about this on Twitter and Shane O’Neill (@SOZDBA) gave me a nudge in the right direction towards finding out the root of the problem by suggesting I look at the error in more detail.  So I wrapped the command in a try/catch block piping the exception to a select *

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfoExtended") | Out-Null;
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null;
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

$InputFile = "E:\Results\27\ResultDir\ReplayResult.trc";
$TableName = "ReplayResults_2"

$conn = New-Object "Microsoft.SqlServer.Management.Common.SqlConnectionInfo";
$conn.ServerName = ".";
$conn.DatabaseName = "ReplayAnalysis"
$InputTrace = New-Object "Microsoft.SqlServer.Management.Trace.Tracefile";
try {
$InputTrace.InitializeAsReader($InputFile);
}
catch
{
$_ | select *
}

Now I had a more detail about the exception being raised:

Exception : System.Management.Automation.MethodInvocationException: Exception calling “InitializeAsReader” with “1” argument(s): “Failed to initialize object as reader.” —>
Microsoft.SqlServer.Management.Trace.SqlTraceException: Failed to initialize object as reader. —> System.IO.FileNotFoundException: Could not load file or assembly
‘file:///C:\Program Files\Microsoft SQL Server\130\Tools\Binn\pfclnt.dll’ 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.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark&
stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm,
Boolean forIntrospection, Boolean suppressSecurityChecks, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile)
at Microsoft.SqlServer.Management.Trace.TraceUtils.CreateInstance(String assemblyRelativePath, String objectClass)
at Microsoft.SqlServer.Management.Trace.TraceFile.InitializeAsReader(String fileName)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Trace.TraceFile.InitializeAsReader(String fileName)
at CallSite.Target(Closure , CallSite , Object , Object )
— End of inner exception stack trace —
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

A quick Google search for pfclnt.dll landed me on a old Connect item that pointed out that the SQL Server Profiler supporting DLL’s for SMO are 32-bit only and to use them in .NET you must compile your assembly for x86.  So the answer was pretty simple, start Windows PowerShell (x86) from the Start menu, paste the same exact script into the ISE window and run it and it works as expected.