As I write this blog post, I am sitting in my hotel room after a long day of Midwest Management Summit at the Mall of America (MMSMOA) sessions. Here I am documenting what happened after I received a MP function error.
Let me start from the beginning. Like a good presenter, I try to avoid any reliance on the internet, so before I left for MMSMOA, I copied my SCCM lab database directly to my laptop. I even tested it before I left by using the re-attach method. Everything worked fine for all of my MMSMOA demonstrations, so I thought I was in good shape. Unfortunately, shortly after I arrived in Minnesota, things fell apart.
MP Function Error Message
I started my day out by trying to get some, “tickets,” closed. This particular one required me to make a small change to a couple of upcoming MP reports that I’m currently working on for Enhansoft Reporting. As I tested them, I received an error message in SQL Server Data Tools (SSDT). I thought, “Why is this happening now?”
In order to completely see the error message, I copied my query (which I knew worked in SQL Server Management Studio (SSMS)) and ran it. Below is the error message in its entirety.
Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly ‘smssqlclr, Version=220.127.116.11, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
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 arssemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
What is this error message telling me? In a nutshell, it’s telling me to fix the MP functions which use a .NET function within the SCCM database. That’s great, but how do you fix it?
MP Function Solution
After some searching, I found several options to solve this problem. Of course, I tested each one! Out of all of them, here’s the simple fix.
Open SSMS, paste the following SQL Server command and then edit the database name to match your database’s name. Finally, execute the SQL Server command.
ALTER DATABASE [CM_Cb1] SET TRUSTWORTHY ON
Once I ran this command, my SQL Server queries for MP details started to work correctly and I went back to what I was originally testing.
By the way, if you are working with a brand-new SQL Server, you might also need to adjust the CLR setting. For more information about how to do that, see my blog post, How Do I Enable CLR within SQL Server?
If you have any questions about MP function error solutions, please feel free to contact me at @GarthMJ.