Visual Studio 2012, Not generating .XMLSerializers.dll with my webservice SQL CLR User Defined Function

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    in Visual Studio I created a Solution where I have two projects.

    Project A is a Sql Server CLR project Target is Sql Server 2008 and .Net 3.5

    Project B is a C# class where my webservices are at.

    I didn't notice this problem straight away, I knew i was missing the XMLSerilizers but when I loaded the .dll into Sql Server 2014 (dev box) I was able to hook in my udf call to the SQL CLR and it calls the function just fine.

    My production box is a Sql Server 2008 R2 but of course now I get the following error:

    Msg 6522, Level 16, State 1, Line 1

    A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_myFunction":

    System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer.

    I'm guessing it's because I don't have the XMLSerializer dll like I do in my previous calls (all built with Visual Studio 2005 pro many boxes ago). I do not have that version of visual studio, and I can't seem to get the XMLSerializer dll at all?

    has anybody rebuilt a webservice call using a user defined function using the newer Visual Studio versions and still gotten an XMLSerializer? if so what were the steps?

    my code is in the Sql Project as shown below; typically I would have built the method on the C# class but I already had it written this way (simplified for the example) but it works in sql 2014 and not on 2008R2... thoughts?

    private class myRows

    {

    public SqlInt32 PKID;

    public SqlString strValue;

    public myRows(SqlInt32 pkid, SqlString strvalue)

    {

    PKID = pkid;

    strValue = strvalue;

    }

    }

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "getSAPFields_FillRow",

    TableDefinition = "pkid int,Field1 nvarchar(20)")]

    public static IEnumerable getMyFields(string strSN, string strModel)

    {

    ws_serverCall.BAPI_ITOB outITOB = new ws_serverCall.BAPI_ITOB();

    ws_serverCall.WS_SERVERCALL ws = new ws_serverCall.WS_SERVERCALL();

    ws.WS_SERVERCALL(strModel, strSN, out outDATA);

    ArrayList myArray = new ArrayList();

    myArray.Add(new myRows(-777, outDATA.FieldData)));

    return new ArrayList(myArray);

    -- Francisco

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    It looks like for Visual Studio 2012, I need to run sgen from the Visual Studio Command Prompt like this: sgen path to .dll and you get the dllName.XMLSerializer.dll file 😀

    Next Question... after all that, it seems I still get the error of: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer.

    -- Francisco

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    I had to run sgen on my class and add it to my assembly as well..

    hope this helps someone too.

    -- Francisco

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply