SQL Server Q&A

Symptoms
When you use a common language runtime (CLR) object in Microsoft SQL Server 2005, you may receive an error message that is similar to the following:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate ‘ObjectName’:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. —> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] s
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)
at System.Xml.Serialization.XmlSe…For example, you may receive the error message when you use a CLR object that calls a Web service or performs conversion from user-defined types to XML inside SQL Server.
Resolution
This issue occurs when a CLR object is converted to the XML data type. When this conversion occurs, the Windows Communication Foundation (formerly code-named “Indigo”) tries to do the following: Generate a new XML serialization assembly.Save the assembly to disk.Load the assembly into the current application domain. However, SQL Server does not allow for this kind of disk access in the SQL CLR for security reasons. Therefore, you receive the error message that is mentioned in the “Symptoms” section. Several scenarios may cause the CLR object to be converted to the XML data type.
For more information about the Windows Communication Foundation, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms735119.aspx(http://msdn2.microsoft.com/en-us/library/ms735119.aspx)You may receive the error message that is mentioned in the “Symptoms” section in the following scenarios: The CLR code that implements CLR objects explicitly uses the XmlSerializer class. These CLR objects may include stored procedures, functions, user-defined types, aggregates, and triggers.You use a Web service in the CLR code.You send or receive CLR objects to or from SQL Server by using direct HTTP/SOAP access to SQL Server. The CLR object converts a user-defined type to the XML data type.

Symptoms
When using SQL CLR stored procedures, user defined functions, user defined data types, or user defined aggregates, you many notice one or more of the following error messages being logged in the SQL server error log when running on a 32 bit instance of either SQL Server 2005 or 2008 
Error message 1
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0×8007000e.
You may fix the problem and try again later.
Error message 2
Msg 6511, Level 16, State 20, Line 1
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0×80131022.
You may fix the problem and try again later.
Error message 3
Msg 6512, Level 16, State 27, Line 2
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0×80004005.
You need to restart SQL server to use CLR integration features.
Error message 4
Msg 6513, Level 16, State 27, Line 1
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure.
Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration
features.
Error message 5
6513: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure.
This is probably due to memory pressure in the MemToLeave region of memory.
For more information, see the CLR integration documentation in SQL Server Books Online.
Error message 6
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0×80131522.
You need to restart SQL server to use CLR integration features.
Additionally, you may see the above error messages with SQL Server 2008 features which use SQL CLR internally, like DMF (Declarative Management Framework) and spatial data types.
Resolution
The error message and HRESULT of the CLR exception can be used to identify the probable cause and likely resolution of the exception
For error messages 1 through 5 above, the most probable cause of these errors is SQL CLR encountering memory pressure. For further information on how to resolve these errors refer to the following article: 969962  Various memory errors are logged to SQL Server error log when using SQL CLR objects
For error message 6, one probable resolution is to reinstall.NET Framework 2.0. To obtain the .NET Framework 2.0, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=0856EACB-4362-4B0D-8EDD-AAB15C5E04F5&displaylang=en