SQLCLR Serializer

• Mike.Hahn

  SSCarpal Tunnel

  Points: 4053

  More actions

  May 5, 2016 at 9:38 pm

  #316286

  Not sure if this is a .Net or SQL question but here goes.

  I created a .Net SQLCLR function that gets data from a web service and returns a table to the user through a table function. The .Net library I wrote has 2 classes. Each goes to a different web service. One web service returns a datatable object and the other a JSON recordset. To use the native .Net classes I manually convert the JSON to XML and use the XML serializer that is supported in SQL. I create the serializer dll in VS2012 by checking the box that says to create the serializer to "ON", SGEN.exe is not on my machine, since the function to create it on the fly by SQL is disabled.

  I load the 2 assembies and it works fine for the first class but the 2nd class that I parse JSON to XML doesn't work because it's trying to create the serializer instead of using the one I created. Works fine on my local machine where I have all the functions on.

  Question:

  1. Do I need a serializer for each class even though it's on 1 dll file, if so how do I get VS2012 to create 2?

  2. What's the option I need to enable to allow SQL server to create the serializer at run time?

  I guess question #2 is more for this forum but I know there's some REALLY smart people here so maybe someone can help. My other option is to create 2 separate projects with each class in it's own DLL.

  Is there a requirement that I only put 1 class in a SQLCLR?

  much appreciated for any suggestions.

  ---------------------------------------------------------------
  Mike Hahn - MCSomething someday:-)
  Right way to ask for help!!
  http://www.sqlservercentral.com/articles/Best+Practices/61537/
  I post so I can see my avatar :hehe:
  I want a personal webpage 😎
  I want to win the lotto 😀
  I want a gf like Tiffa :w00t: Oh wait I'm married!:-D