Share via

How to consume a web service from within SQL Server using SQL CLR

  • Article

Today I’ll show how you can consume a Web Service from within SQL Server using the SQL Server CLR.

 

DISCLAIMER:

This is just intended as proof of concept. I do feel that if you wish to consume web services from SQL Server, then you

should probably put this code in an assembly outside SQL Server and then call that assembly from the CLR within SQL Server.

This will make it more maintainable when, for example, the web service changes. If you then need to rebuild the references etc.,

it will be easier to do this in an external assembly rather than in assemblies stored in SQL Server.

 

In this scenario we have a web service that return the current temperature for the provided city. We wish to consume this information

from within SQL Server and store it in a table. Let’s do this from scratch.

 

Step 1 is to create the web service itself.

In Visual Studio, create a new ASP.Net Web Service project in a web site (called Weather in this example) call it “WeatherService”.

Delete the default WebMethod and create a new one that will return the temperature for provided city. Call this method “GetTemperatureForCity”.

The full code for this web service then should look like this:

 

public class WeatherService : System.Web.Services.WebService {

    public WeatherService ()

    {

    }

    [WebMethod]

    public string GetTemperatureForCity(string city)

    {

        // Obviously not resembling what a real service would do.

        string temperatureInfo;

        Random r = new Random();

        switch (city.ToUpper())

        {

            case "STOCKHOLM" :

      case "LONDON" :

            case "NEW YORK" :

            // etc

                temperatureInfo = GetWeatherString(city.ToUpper(), r.Next(-50, 50));

                break;

            default:

                temperatureInfo = String.Format("No temperature found for {0}.", city.ToUpper());

                break;

        }

        return temperatureInfo;

    }

    private string GetWeatherString(string city, int temp)

    {

        return String.Format("Temperature in {0} is {1} C.", city, temp);

    }

}

 

Test this service to make sure it works, for example, by running: https://localhost/Weather/WeatherService.asmx?op=GetTemperatureForCity (assuming that you are on localhost).

 

Step 2 is to create a new Database – Visual C# SQL CLR Database Project (I’ve used .Net 2.0) in Visual Studio. Call it “WeatherConsumer”

Once the project has been created, right click it and select Add – User-Defined Function, call it “WeatherFunction”.

Now, right click References and Add Web Reference. In the URL field, enter the URL for your web service (https://localhost/Weather/WeatherService.asmx for me).

This should list the “GetTemperatureForCity” operation if the web service is found. Rename the “Web Reference Name” to “WeatherReference”.

Edit the code so it looks as follows:

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString WeatherFunction(string city)

    {

        WeatherConsumer.WeatherReference.WeatherService ws = new WeatherConsumer.WeatherReference.WeatherService();

        return ws.GetTemperatureForCity(city);

    }

};

 

Then changed the build type to Release and build it.

 

[Detour]

Why not deploy directly from Visual Studio you ask?

Well, since a web service returns XML that is used for serialization it will require a dynamically generated assembly.

This assembly will not be on the SQL server if you just deploy it from Visual Studio. So while the deploy will work, it will fail with:

 

Msg 6522, Level 16, State 2, Line 2

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

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 option

        ...

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.GetSerializersFromCache(XmlMapping[] mappings, Type type)

   at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)

   at System.Web.Services.Protocols.SoapClientType..ctor(Type type)

   at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()

 

when you are trying to use the function. There are ways around this and one easy way is the way below.

[End of detour]

 

So when you have built it in release mode, then you should in your Release directory have two dll files: “WeatherConsumer.dll” and “WeatherConsumer.XmlSerializers.dll”.

Leave them there or copy them to better location. I’ve copied them to C:\WeatherCLR.

 

Step 3 is to start SQL Server Management Studio and select the database that you wish to have these assemblies in.

Then execute the following to create table to store info in, to create the assemblies in the server, map the function and to insert one row.

 

-- Create table to store weather info in

create table WeatherInfo (id int identity primary key, info nvarchar(200))

go

-- Create the assemblies from the ones we have built.

create assembly [WeatherConsumer] from 'C:\WeatherCLR\WeatherConsumer.dll' with permission_set = external_access

create assembly [WeatherConsumer.XmlSerializers] from 'C:\WeatherCLR\WeatherConsumer.XmlSerializers.dll' with permission_set = external_access

go

-- Create the function that is to be used for getting the weather info.

create function CityTemperature(@inval nvarchar(200)) returns nvarchar(200)

as external name WeatherConsumer.UserDefinedFunctions.WeatherFunction

go

-- And get and insert the temperatures from the webservice.

insert into WeatherInfo (info) select dbo.CityTemperature('London')

-- and check outcome

select info from WeatherInfo

 

Note that we need EXTERNAL ACCESS permission set for the assemblies; this is because this codes goes outside the allowed boundaries when using the web service reference.

If you would run with the default permission set (SAFE) you would get the following when using the function:

 

Msg 6522, Level 16, State 2, Line 1

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

System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

   at System.Security.CodeAccessPermission.Demand()

   at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)

   at System.Net.HttpRequestCreator.Create(Uri Uri)

   …

 

You can test this by changing the permission set for the assemblies, like so:

 

alter assembly [WeatherConsumer] with permission_set = safe

alter assembly [WeatherConsumer.XmlSerializers] with permission_set = safe

go

 

See more here:

"CLR Integration Code Access Security"

https://msdn.microsoft.com/en-us/library/ms345101.aspx

 

So there you go, a web service consumer running within the SQL Server CLR. As mentioned, I’m not sure it this is to recommend, this is just to show that it is possible.

Comments

  • Anonymous
    November 25, 2010
    Will this technique work well if my CLR is in say a stored procedure and it is scheduled to run by the SQL Server Agent?

  • Anonymous
    December 15, 2011
    Hi Mike, where can I get your email , I need to contact you ;)

  • Anonymous
    May 28, 2013
    The comment has been removed

  • Anonymous
    October 02, 2013
    Interesting! And is it possible to call the external web services for which one does not have an assembly (i.e. by importing WSDL)?

  • Anonymous
    January 13, 2014
    i get an error on SQL server because the  amount of data returned from the web service exceeds the minimum data allowed: The maximum string content length quota (8192) has been exceeded while reading XML data. How can i change the maximum string content length quota?

  • Anonymous
    March 03, 2014
    I am getting error while running below in sql server EXTERNAL ACCESS ASSEMBLY permission was denied on object 'server', database 'master'. while running below -- Create the assemblies from the ones we have built. create assembly [WeatherConsumer] from 'C:WeatherCLRWeatherConsumer.dll' with permission_set = external_access create assembly [WeatherConsumer.XmlSerializers] from 'C:WeatherCLRWeatherConsumer.XmlSerializers.dll' with permission_set = external_access go