Using Webservices and Xml Serialization in CLR Integration

Last time we saw an example of using a powerful functionality, Regular Expressions, from a supported .NET Framework library and how you can leverage it in your database applications.

Today we will talk about another such functionality that is enabled by CLR Integration. CLR Integration allows you to consume results from webservice calls. As you might already know, there are three permission buckets in CLR Integration – SAFE, EXTERNAL ACCESS and UNSAFE. Calling webservices is a typical EXTERNAL ACCESS scenario and requires you to register your assembly in the EXTERNAL ACCESS permission bucket. 

Xml Serialization

Calling webservice methods requires serializing all the types being used to xml. Outside SQL Server, this serialization code is generated at runtime, compiled and loaded in the application. However, for security and reliability reasons, SQL Server does not allow you to dynamically load assemblies. Hence, to make this scenario work, you need to pre-generate the serialization assembly and register it in the database. Visual Studio 2005 beta2 allows you to generate the serialization assembly from your project. You need to turn the Generate serialization assembly option on in project properties à Build. You can also generate the serialization assembly using a tool called ‘sgen’ that is shipped with the .NET Framework SDK as follows:

>sgen.exe myAsm.dll

Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.

Once you have these two assemblies available. You need to register them in SQL Server as follows:

CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’

with permission_set = EXTERNAL ACCESS

CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’

with permission_set = SAFE

Visual Studio 2005 allows you to register the first assembly in SQL Server using the deploy feature in SQL Server projects. However, it does not support deploying the serialization assembly and you would need to manually register it. If you are using Visual Studio on the same machine as your SQL Server, you can use the pre / post deploy script feature in Visual Studio to automate this in your project. This can be done as follows:

1. Add the following to a file called predeployscript.sql:

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N’myAsmXML‘)


2. Add the following to a file called postdeployscript.sql:

CREATE ASSEMBLY myAsmXML from ‘<path>\myAsm.XmlSerializers.dll’

3. Add these two files to your project by right clicking your project in Solution Explorer and Add à Existing Item. 

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run these scripts before and after the deployment of your project. However since the scripts are run on the SQL Server, the path must be relative to the machine on which you have SQL Server. If SQL Server and VS are on separate machines then you must make sure the path is a network path and accessible from SQL Server machine.

Webservice consumption is an example of where xml serialization is required. You might require xml serialization for other purposes as well. Following are two examples where you might require xml serialization:

  1. Converting a User Defined Type (UDT) to XML in SQL Server
  2. Serializing / Deserializing an object to xml in your CLR code. Suppose you are getting an xml blob from somewhere and you would like to deserialize it into the appropriate object.

Both of these cases also require you to generate the appropriate serializer assembly and add it to the database.

Generating Webservice Proxy Classes

Before calling webservice methods in your code, you need to generate the proxy classes for the webservice you are calling. Visual Studio 2005 does this automatically when you add a webreference to your SQL Server project.  However, the auto generated proxy classes by Visual Studio 2005 beta2 contains synchronization code that prevents you from using it in EXTERNAL ACCESS due to reliability restrictions in SQL Server 2005. To make it work in EXTERNAL ACCESS you need to manually generate the proxy classes using wsdl which is another tool shipped with the .NET Framework SDK in the oldAsync mode.  You can do this as following:

>wsdl /par:par.txt /n:<namespace> <url>

Where par.txt is a parameters file passed to wsdl that specifies various options. An e.g. of parameters file that you can use to specify the oldAsync option is as follows:

<wsdlParameters xmlns=’’>






    <codeGenerationOptions>properties oldAsync</codeGenerationOptions>



Once you generate the proxy class using wsdl you can add it to your SQL Server Project in Visual Studio 2005 beta2 by right clicking your project in Solutions Explorer and Add à Existing Item. If you do this, make sure you do not add a webreference to this url in your project.  Visual Studio 2005 RTM will autogenerate the proxy classes in oldAsync mode for SQL Server projects and you would not require to manually generate them as above.


Let me summarize all the steps that are required to call webservice methods in SQL Server CLR Integration using Visual Studio 2005 beta2 SQL Server project:

  1. Generate the proxy classes using wsdl in oldAsync mode. Add the generated class to your project. 
  2. Develop your code.
  3. Generate the serialization assembly using Visual Studio build option or using sgen.
  4. Deploy your project as External Access.
  5. Register the serialization assembly in the database by adding a predeployscript and a postdeployscript in your VS project or using CREATE ASSEMBLY directly from SQL Server Management Studio.

Note: Step 1 would not be required in Visual Studio 2005 RTM. You would be able to add a web reference to your project and that would automatically generate SQL Server External Access compliant proxy classes.

– Vineet Rao

Microsoft SQL Server

This posting is provided “AS IS” with no warranties, and confers no rights. 

Comments (26)

  1. Srinivas Sampath says:

    Would you recommend calling Web Services from SQL Server 2005 as a programming pattern? For example, if we call this as part of a transaction, it would be disastrous, right? I think it would be useful if you could blog about how someone can reliably call web services from the database through the CLR, since I think this is a feature that can be easily misused.

  2. Srinivas Sampath says:

    I was also reading about the CLR integration and wanted to know if there is a way to see the number of AppDomains being created. The documentation and several web-casts say that 1 AppDomain is created per identity in a database, but more will be created "as needed". Can you exemplify this through a blog?

  3. Comment to Srinivas: There is one AD created per database and owner of an assembly. So, if we have two assemblies created in the db, and the two assemblies are owned by different users, and we are executing methods from both assemblies, then there will be two AD’s in the db. SQL then can (supposedly) create more AD’s if necessary. I have however never seen that happen. To view what AD’s you have you can use the system view: sys.dm_clr_appdomains

  4. Himadrish Laha says:

    Is not it better if we call webservice from middle tier. Why need to call from SQL???

    What do you think???



  5. Srinivas Sampath says:

    Niels >> Thanks for the information.

    Himadrish >> Precisely the same question that I also asked. Am not sure if there are many use cases that will require a web-service call from a database.

  6. hubba002 says:


    Can you share with me an entire VS 2005 (1st release code) project for using a webservice in SQL 2005 CLR code?

    I am having diffifculty making this work and I don’t know why. I followed your directions as closely as I could. I still get the following error…

    Msg 6260, Level 16, State 1, Line 1

    An error occurred while getting new row from user defined Table Valued Function :

    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.



  7. Hello Vineet,

    I’ve been trying to serialize a class as xml from inside a CLR stored proc for SQL Server, and I’ve been in a world of hurt.

    My class has a method with this line of code:

    XmlSerializer serializer = new XmlSerializer(typeof(MyClass));

    This is where I get the error: "Cannot load dynamically generated serialization assembly."

    I’ve used sgen to generate the serialization assembly and I have a script that installs it with SAFE permission in SQL Server. I’ve turned the TRUSTWORTHY database property to ON, and I’ve set the permission level for my primary assembly to EXTERNAL ACCESS. After all this, I still get the same exception. Any ideas?

  8. Eron Wright says:

    If you receive "Cannot load dynamically generated serialization assembly", you are likely still using new XmlSerializer(typeof(MyClass)). I believe you must either link to the generated assembly and use new MyClassSerializer(), or use XmlSerializerAssemblyAttribute. The latter approach doesn’t appear to work with SQL Server. I hope I am wrong, because this is becoming quite inconvenient.

    Hope this helps.

  9. I discovered the reason for the exception, "Cannot load dynamically generated serialization assembly," while trying to serialize a class with the following code:

    XmlSerializer serializer = new XmlSerializer(typeof(MyClass));

    It turns out the class I was trying to serialize had a method called "Serialize". When I renamed the method to something else, such as "MySerialize", the exception went away and the SQL-CLR used the assembly I had pre-generated using sgen.

    I’m not sure why a Serialize method would confuse the class loader, except that the class contained in the generated serialization assembly also has a Serialize method, and the CLR uses reflection to invoke the method. This behavior, however, is specific to the SQL-CRL implementation, because the same situation in a Console app does not produce the same result. There the sgen’d serialization assembly is always used if it is present, even if the class has its own Serialize method.

  10. Eron Wright says:

    Actually, I am wrong. XmlSerializer will locate the assembly generated by sgen without using the specialized serializer.

  11. Chad Stoker says:

    Everything works perfectly here. In fact I even went so far as to automate the SGEN process in the "Post-Build" step of the Project Properties by doing this :

    SGEN /force /compiler:/keyfile:R:KeysmyKey.snk /t:myAsm $(TargetDir)$(TargetName).dll

    And that works. HOWEVER, you’ll notice that I’m strongly-naming my assembly and the XmlSerializer assembly that goes with it. See? IMMEDIATELY after strongly-naming the assemblies, I get this error message :

    Msg 6522, Level 16, State 1, Procedure PerformGlobalTokenValueAlteration, Line 0

    A .NET Framework error occurred during execution of user defined routine or aggregate ‘PerformGlobalTokenValueAlteration’:

    System.InvalidOperationException: There is an error in the XML document. —> System.Security.SecurityException: That assembly does not allow partially trusted callers.


    at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

    at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReaderChainDescription.Read2_ChainDescription(Boolean isNullable, Boolean checkType)

    at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReaderChainDescription.Read3_ChainDescription()


    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

    at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader)

    at StoredProcedures.DeserializeObject(XmlReader reader, Type t)

    at StoredProcedures.PerformGlobalTokenValueAlteration(SqlInt32 taskChainID, SqlXml propertyElements)

    The message "That assembly does not allow partially trusted callers." has now popped up because I compiled with an SNK! I’m curious what I do to actually resolve this problem. Would using some [Assert] CAS permissions in my assembly help here? Is there a different Security compilation option that I should be doing in the SGEN command? I’m just curious.


  12. Chad Stoker says:

    Hmmmm, I thought I might solve my own problem. As it turns out, all you’ve got to do to elliminate the "That assembly does not allow partially trusted callers" error when Strongly-typing your assemblies in SQL Server 2005, is add the following to your Assembly.cs file:

    [assembly: System.Security.AllowPartiallyTrustedCallers]


  13. Joel Hammond says:

    I’ve followed your instructions successfully, but I’m having problems debugging.  It seems as though when VS does a build, it creates the XmlSerializers.dll, but if you try to debug, it first does a deployment without creating the serialization dll. I get this…

    Executing deploy script: PostDeployScript.sql …

    Error: executing deploy script failed. CREATE ASSEMBLY failed because it could not open the physical file ‘C:…binSQLFAX.XmlSerializers.dll’: 2(The system cannot find the file specified.).: PostDeployScript.sql

    Can you tel me how to debug a clr stored proc that calls a webservice?


  14. maxambrogi says:

    I’m trying to reproduce the case but i always get the same error message ""Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer." I’m using (succesfully)the following sql code :


    FROM ‘C:devSQLSERVER2005TestLibraryTestLibrarybinDebugTestLibrary.dll’

    WITH permission_set = EXTERNAL_ACCESS ;


    CREATE ASSEMBLY WriteToLogXmlSerializers

    FROM ‘C:devSQLSERVER2005TestLibraryTestLibrarybinDebugTestLibrary.XmlSerializers.dll’

    WITH permission_set = SAFE;




    @fromDB NVarchar(100),

    @fromFacade NVarchar(200) OUTPUT


    AS EXTERNAL NAME WriteToLogStd.[TestLibrary.LogFacade].WriteToLog;


    I’m using (succesfully)the following postbuild event:"C:Program FilesMicrosoft Visual Studio 8SDKv2.0Binsgen.exe" /force /compiler:/keyfile:c:key.snk /t:TestLibrary.LogFacade $(TargetDir)$(TargetName).dll

    Where’s my error?

    Can you help me?

    Best regards

  15. vineet_jaiswal says:


    i created a Dll in, which is accessing a webservice. i am using that dll inside sql server 2005. in sql server i created a function inside which i am using this Dll, again that function is used by a stored procedure. now the problem is that this Dll is working fine but some time it crashes. i.e. some time it doesn’t respond, sometime it takes so much long time to execute which we can’t accept as it is in use for some Bill payment Services. Also sometime i recieve error like ‘Could not Load Assembly…’. Also after Dropping these assemblies and Function and Re-registering them the problem gets removed for some time, but again after 1 or 2 day the same problem gets started.

    Please guide me to solve this problem



  16. Thanks, this is exactly the information I needed.

    Being able to consume data from a WebService and return it as a TBV is very useful.

  17. srikanth says:

    i am going to doing a messanger for intranet application so what is the role of webservices and xml in that

  18. Krystian says:

    I need to create a procedure (SQL 2005). I created dll in C# (Visual Studio 2005). The dll uses web services from MS Project Server 2007. I used sgen to create Xml serialization dll. The problems are when I am trying to register them in SQL server by typing:

    CREATE ASSEMBLY myAsm from ‘<path>myAsm.dll’

    with permission_set = EXTERNAL_ACCESS

    CREATE ASSEMBLY myAsmXml from ‘<path>myAsm.XmlSerializers.dll’

    with permission_set = SAFE

    I am getting errors:

    CREATE ASSEMBLY failed because method "add_LoginCompleted" on type "function_name.LoginWindowsWebSvc.LoginWindows" in safe assembly "myAsm" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies

    CREATE ASSEMBLY failed because method "add_LoginCompleted" on type "function_name.LoginWindowsWebSvc.LoginWindows" in external_access assembly "myAsm" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies.

    any ideas how to solve that?

    my email is:

    any help

  19. Simple question: what security permission level is required to allow dynamic loading of assemblies? Trick

  20. I’ve done a lot of things from the SQLCLR thus far, except for consuming an ASP.NET Web Service. This

  21. 轻轻松松 says:




  22. hot free myspace layouts codes premade icp

  23. Karim's Blog says:

    Today I’ll demonstrate a very nice feature that has been added recently in Microsoft SQL Server 2005…

  24. In yesterday’s posting the BDC was used to present data from both Project Server and the AdventureWorks