SQL SERVER – System.IO.FileNotFoundException: Could not load file or assembly

In a recent R&D operation, I was trying to use some SQLCLR assembly as part of the installation process. It was not easy but it got me into a mess that I thought was worth debugging. One of the interesting issues which I faced recently was to deploy a .net assembly which was failing with an error. I thought the third party .msi and the .dlls they shared must have gone wrong. I wanted to understand what was going wrong here to get a better feeler. The Error mentioned as part of the stack was:

Error: System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.SqlServer.BatchParserClient, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.SqlServer.BatchParserClient, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’
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.Common.ServerConnection.GetStatements(String query, ExecutionTypes executionType, Int32& statementsToReverse)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Whenever there are such errors, we need to look at a few things.

1. What is the error?
2. What is assembly name?
3. What is the version of the assembly?

Here is what we have in error message. I have highlighted them as well.

1. IO.FileNotFoundException: Could not load file or assembly
2. SqlServer.BatchParserClient
3. Version=12.0.0.0

The assemblies are stored in C:\Windows\Assembly folder and they are shown as below

Two things to be noted in the screenshot. Version and Processor Architecture. The screenshot is NOT from the same machine where we received the error. On the problem machine, we were not seeing the needed assembly.

I have downloaded and installed “Microsoft® SQL Server® 2014 Shared Management Objects” (SMO) from https://www.microsoft.com/en-us/download/details.aspx?id=42295 and after that I was able to get the assembly and the code worked. Microsoft SQL Server Management Objects require – Microsoft SQL Server System CLR Types, that was also available on the same page.

I am sure most of us sometimes don’t get on this path of exploration, but I felt this was a great learning for me to explore. Do let me know if you did debug anything on these lines at your environments via the comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)