1

I am encountering a problem with running stored procs that execute methods within a custom .Net assembly I have built and fully unit tested. In SQL Server 2014 my solution works and the stored proc executes just fine, its only when I use SQL Server 2008 R2 that I get these errors.

The problem is when I add my assembly to SQL Server 2008 R2, create my stored procs, and execute one of them like this:

 EXEC dbo.Avatas_CallAddCustomerAndAddCreditCardService 4111111111111111, '1025' , 925 , 
      3500, '20/20' , 'asdf' , 1111, 1111 , '600094' , '0442375110' , 
      'asdf' , 'asdf' , '01' , NULL , 7777

I get the following error:

Msg 6522, Level 16, State 1, ProcedureAvatas_CallAddCustomerAndAddCreditCardService, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "Avatas_CallAddCustomerAndAddCreditCardService": 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.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters 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, String location, Evidence evidence) at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlAttributeOverrides overrides, Type[] extraTypes, XmlRootAttribute root, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlRootAttribute root) at FirestreamAvatasCustomerVault.AscendAvatasInt35.AvatasWebServiceCaller.DeserializeAvatasResponse(String response) at FirestreamAvatasCustomerVault.AscendAvatasInt35.AvatasWebServiceCaller.CallManageCustomerService(Int32 personId, String address1, String address2, Int32 cityId, Int32 stateProvId, String zip, String phone, String firstName, String lastName, String company, Nullable1 customerVaultId, Nullable1 clientId) at FirestreamAvatasCustomerVault.AscendAvatasInt35.AvatasWebServiceCaller.CallAddCusto...

I have changed Generate Serialization Assembly to On and have the XMLSerializers.dll produced by it registered as well as the actual dll registered.

Here is my stored procedure and assembly creates:

CREATE ASSEMBLY [Avatas]
AUTHORIZATION [dbo]
FROM 'D:\TFS Workspace\Firestream\Dev\AvatasCustomerVault\FirestreamAvatasCustomerVault\FirestreamAvatasCustomerVault.AscendAvatasInt35\bin\.Net 3.5'
WITH PERMISSION_SET = UNSAFE
CREATE ASSEMBLY [Avatas.XmlSerializers]
AUTHORIZATION [dbo]
FROM 'D:\TFS Workspace\Firestream\Dev\AvatasCustomerVault\FirestreamAvatasCustomerVault\FirestreamAvatasCustomerVault.AscendAvatasInt35\bin\.Net 3.5\FirestreamAvatasCustomerVault.AscendAvatasInt35.XmlSerializers.dll'
WITH PERMISSION_SET = UNSAFE

exec('CREATE PROCEDURE [dbo].[Avatas_CallAddCustomerAndAddCreditCardService](@creditCardNumber bigint, @expirationDate nvarchar(10), @ccv int, 
         @personId int, @address1 nvarchar(100), @address2 nvarchar(100), 
@cityId int,
         @stateProvId int, @zip nvarchar(50), @phone nvarchar(20), @firstName nvarchar(150), @lastName nvarchar(150), @company nvarchar(150), @customerVaultId int, @clientId int)
AS EXTERNAL NAME [Avatas].[FirestreamAvatasCustomerVault.AscendAvatasInt35.AvatasWebServiceCaller].[CallAddCustomerAndAddCreditCardService]')

The actual line that the error occurs on is as follows:

XmlSerializer serializer = new XmlSerializer(typeof(AvatasResponse), xRoot);
Aaron Davis
  • 281
  • 1
  • 5
  • 22
  • Oh yeah I also have ensured I have trustworthy on – Aaron Davis Sep 18 '15 at 20:03
  • Could it be a versioning issue? Off the top of my head I'm unsure which version of the CLR 2014 uses, but if your assembly is coded into that then I suppose that might cause another assembly not to be loaded as a result – Rachel Ambler Sep 18 '15 at 20:08
  • Oh yeah I should have mentioned that, I actually have the assembly compiling into .Net 3.5 so I don't think it is a versioning issue, I also have the NewtonsoftJson package I am using compiling down to .Net 3.5 – Aaron Davis Sep 18 '15 at 20:09
  • This is probably a duplicate of http://stackoverflow.com/questions/31570763/cannot-load-dynamically-generated-serialization-assembly-error-in-sqlclr-store Is there any info there that helps? It seems that 2008 R2 is common between the two issues. One major difference between 2008 R2 and 2014 is that 2008 R2 uses CLR v2 while 2014 uses CLR v4. It _could_ also be that Microsoft changed the properties of the CLR host between the versions and didn't document it. The error does say "LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host." – Solomon Rutzky Sep 18 '15 at 20:31
  • I looked at that question, and I did try changing the permissions to SAFE, EXTERNAL ACCESS, and UNSAFE and none of those helped. The actual line where things break is now included in my post above and its on the XMLSerializer class initialization which is not one of those excluded methods – Aaron Davis Sep 18 '15 at 20:46
  • @AaronDavis Does the `AvatasResponse` class have a method named "Serialize" in it? If yes, can you try renaming it to something else? – Solomon Rutzky Sep 18 '15 at 21:59
  • Not clear from the question for me. After getting that error, you generated serialization dll and added that to sql server, but still have the same error? – Evk Sep 18 '15 at 22:04
  • SQL 2014 and SQL 2008 R2 use different versions of .NET. http://blogs.msdn.com/b/dohollan/archive/2012/04/20/sql-server-2012-sqlclr-net-framework-version.aspx – Ben Thul Sep 18 '15 at 22:10
  • @BenThul Yes, they use different versions of the CLR, but that is not the issue here. – Solomon Rutzky Sep 18 '15 at 22:20
  • @srutzky: Cool. It is a difference, though. I'm interested to see what you come up with as being the problem! – Ben Thul Sep 18 '15 at 22:42
  • @evk yes I still get the same error just stating what I have tried – Aaron Davis Sep 18 '15 at 23:45
  • @ben thul yes I compiled my code down to .net 3.5 which seems to work with both – Aaron Davis Sep 18 '15 at 23:46
  • @surutzky the actual method causing the issue is in system.runtime.serialization and it's the constructor for the xmlserializer class – Aaron Davis Sep 18 '15 at 23:47
  • Aaron: I understand both of those points. But my question was very specific and there is a reason: I found a reference to someone else having this exact same problem and they reported that this was the issue and changing the name of that method fixed it. So, does your `AvatasResponse` class have a method named "Serialize" in it? – Solomon Rutzky Sep 19 '15 at 00:15
  • It has a method named deserializeavatasresponse could that be the issue? That would be very odd I'll try renaming it – Aaron Davis Sep 19 '15 at 00:18
  • Aaron, I am not sure about that as there was only mention of the method being named specifically "Serialize" since SQL Server's CLR host was using reflection to find specific methods. Though based on that scenario, it would seem likely that having a method named "Deserialize" would also cause this problem. another thing to look for is, I saw mention somewhere of someone claiming it might be due to having a method decorated with a serialization attribute that was not liked by SQL Server's CLR host. – Solomon Rutzky Sep 19 '15 at 01:18
  • Aaron, any more info? – Solomon Rutzky Oct 01 '15 at 01:39
  • The only thing I could figure out is xmlserializer class must not be supported for use in SQL server 2008 r2 so I removed it and used json instead of xml to get things to work – Aaron Davis Oct 01 '15 at 01:59

1 Answers1

0

The final solution I found was that SQL Server 2008 and 2008 R2 only support dlls compiled up to version 3.5 of .Net Framework. Whereas SQL Server 2012+ only support version 4 and above.

Aaron Davis
  • 281
  • 1
  • 5
  • 22