Calling A Web Service From SQL Server 2005

This may not be your daily routine but in today’s world of interoperability and loosely-coupled systems you sometimes don’t have another option.  Imagine a situation where a scheduled task (using SQL Agent) needs to get data from a web service or trigger some code through a web service.

In the following explanation I will show how to use SQL CLR (Common Language Runtime) Integration to get a weather report from a web service, or WS in short.

For this I am using a free web service: http://www.webservicex.net/globalweather.asmx.  Or not…  Seems that the weather web service gods don’t like me, this WS has (temporarily?) stopped working after I completed the sample and part of this article.  I haven’t found a replacement that can deliver global weather so we’ll have to do with US-only, delivered through this web service: http://ws.cdyne.com/WeatherWS/Weather.asmx.

Now is a good time to warn you about the usage of external services.  If you need your code to work all the time, make sure that you can trust the web services that you use.  Otherwise you may just end up as I did, with a dead function…

Basic knowledge on building .NET assemblies and using SQL Server is required.

The assembly

Let’s start with creating our .NET assembly with the custom stored procedure.  Using Visual Studio 2005, create a new Database Project:

Database_project

Add a Web Reference to your project pointing to the .asmx mentioned earlier.

Add_Web_Reference

Add a Stored Procedure to your project:

WeatherSP

This class is going to contain the code to call the web service and return a result to the client.

Following code fetches the weather report for a given US Zip code.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MyStoredProcedures.com.cdyne.ws;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void WeatherSP(string zipCode)
    {
        Weather myWeatherWS = new Weather();
        WeatherReturn weatherResult = myWeatherWS.GetCityWeatherByZIP(zipCode);

        SqlMetaData[] recordMetaData = new SqlMetaData[2];
        // layout of the records that we'll return
        recordMetaData[0] = new SqlMetaData("Description", SqlDbType.Char, 100);
        recordMetaData[1] = new SqlMetaData("Value", SqlDbType.Char, 1000);

        // build a record based on the metadata
        SqlDataRecord record = new SqlDataRecord(recordMetaData);

        // let's start sending result into the active pipe
        SqlContext.Pipe.SendResultsStart(record);

        // one way of adding a new record to the pipe
        record.SetString(0, "ResponseText");
        record.SetString(1, weatherResult.ResponseText);
        SqlContext.Pipe.SendResultsRow(record);

        // another way of adding a new record, using an array initializer
        record.SetValues(new String[2] { "City", weatherResult.City });
        SqlContext.Pipe.SendResultsRow(record);

        record.SetValues(new String[2] { "State", weatherResult.State });
        SqlContext.Pipe.SendResultsRow(record);

        record.SetValues(new String[2] { "Description", weatherResult.Description });
        SqlContext.Pipe.SendResultsRow(record);

        record.SetValues(new String[2] { "Temperature", weatherResult.Temperature });
        SqlContext.Pipe.SendResultsRow(record);

        record.SetValues(new String[2] { "Timestamp", DateTime.Now.ToString() });
        SqlContext.Pipe.SendResultsRow(record);

        // finished sending result
        SqlContext.Pipe.SendResultsEnd();
    }

};

In the code above you can see that the stored procedure has a parameter called zipCode.  This incoming value is passed to the web service call.

Our result consists of several records, each record containing 2 string fields.

The web service that we’ve used returns an object of type WeatherResult.  This makes it easy for us to read the result.  The WS that I used initially didn’t return an object but an XML string.  In that case you can use the XmlDocument.LoadXml from the System.Xml namespace to get an XmlDocument.  Then use the SelectSingleNode method to get to the details, for instance myXmlDoc.SelectSingleNode(“//Temperature”).InnerText would return the value for the Temperature node.

And this is what it looks like when our stored procedure gets called from the Management Studio:

WeatherSP_SSMS_result

Building the assembly

Before we build our assembly there are a couple of project settings that need modification.

Here we ask Visual Studio to also generate an assembly containing the XML Serializers.  This is needed because our code is calling a web service and code running in SQL Server is not allowed to use the serializers that are normally generated dynamically.

Project_properties_Build_tab

Another setting that we need to change is the Permission Level.  This is also required because our code is calling a web service, hence external.

Project_properties_Database_tab

Server and Database Settings

Now that we’ve covered the actual development of the stored procedure we would like to install it on our database server.  This requires us to modify some settings on the server and database.  If you are not the DBA of the server and you are in the luxurious situation that such a person exists, please verify with him/her if you are allowed to modify these settings.

Enabling CLR Integration

By default SQL Server does not allow CLR Integration.  This setting can be easily modified with the following script:

exec sp_configure 'clr enabled', '1';
reconfigure;

Our database we trust

As our stored procedure needs external access permission, we need to create the assembly with external access (as shown in next chapter).  To get this to work we need to convince SQL Server that our database can be trusted: (I’ve used AdventureWorks in this article but this can be any existing database)

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON;

Owner needs external access

To be allowed to create an assembly with external access, the database owner needs to have EXTERNAL ACCESS ASSEMBLY permission.  If that’s not the case you will see the following error when trying to create the assembly:
Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly ‘MyStoredProcedures’ failed because assembly ‘MyStoredProcedures’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
So to avoid this error make sure that your DB owner has sufficient permissions.  The following script makes sa the owner of the active database:
EXEC sp_changedbowner 'sa', false;

Installing the Stored Procedure

We’re almost there, all that remains to be done is to tell SQL Server where our stored procedure can be found and that it actually exists.  Assemblies, just like stored procedures, live in a database (AdventureWorks in our case).  In Management Studio you can see your assemblies in the Object explorer under the Programmability node of your database:

SSMS_assemblies

Adding the assemblies

Following script adds the two assemblies that we’ve created earlier to your database.  An assembly is given a name, like MyStoredProcedures.  Here you can finally see the EXTERNAL_ACCESS permission that we need for our web service call.

CREATE ASSEMBLY MyStoredProcedures
FROM 'D:\vvr\techtests\dotnet\MyStoredProcedures\MyStoredProcedures\bin\Debug\MyStoredProcedures.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE ASSEMBLY MyXmlSerializers
FROM
'D:\vvr\techtests\dotnet\MyStoredProcedures\MyStoredProcedures\bin\Debug\MyStoredProcedures.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

Adding our stored procedure

Last step is to create our stored procedure.  We tell SQL Server that it can be found in the MyStoredProcedures assembly, in the class StoredProcedures and that the method is called WeatherSP.  We have one parameter of type string in our method definition, which translates to nvarchar in the stored procedure.  A length of 10 is more than enough for our ZIP code.

CREATE PROCEDURE WeatherSP
    @zipCode nvarchar(10)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;
GO

Configuring the Stored Procedure

In some (most?) cases we don’t want to hard-code connection details. In this case it means the path to the web service. Imagine a situation where you are consuming a web service that you’ve developed yourself, or another team in your company. And the web service is running in several environments: development, acceptance, production. Of course we don’t want to change our code and recompile each time we need to deploy to a different environment. So we need a way to configure the web service location.

The answer here lies in the sqlservr.exe.config file. This is a file like any other .config file, with a possible content like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="MyWebService" value="http://MyWebServiceServer:MyWSPort/PathTo/MyWebService.asmx" />
  </appSettings>
</configuration>

This file needs to be put in the same folder as your sqlservr.exe that’s running your SQL Server instance.  By default this path is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\.  When you restart your SQL Server instance while this file is in that folder, SQL Server will pick up any configuration settings from that file.  These can then be accessed from your custom stored procedure in this way:

/* The following call is made in order to workaround an "anomaly"
   * where reading an appSettings key value results in the error:
   * "The value of the property 'key' cannot be parsed.
   * The error is: Request failed. (sqlservr.exe.Config line X)"
   *
   * So, just a call to another section alleviates the issue.
   * This error only occurs on the first call to an assembly after
   * it's been loaded by the sqlservr.exe
   */
int firstcall = ConfigurationManager.ConnectionStrings.Count;

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];
myWS.MyWebMethod();

That’s it, that’s all it takes.  It’s not that difficult, once you know it, right? :-)

Have fun!

Valentino.

Share

Tags: , , , , , , ,

  1. mails-guide.com’s avatar

    This is my first time visit here. From the huge amounts of remarks on your articles,I surmise I am not just one having all the happiness directly here!

    Reply

  2. Exclusief zwembad’s avatar

    That is an uncommon idea you have, and yes this would use the information you have given us through this blog. I will grant your contemplations to all of my sidekicks.

    Reply

  3. Zwembad laten bouwen’s avatar

    Decent to visit your blog once more, it has been a very long time for me. Well this article i’ve been sat tight for such a long time. I need this article to finish my task in the school, and it has same theme with your article. Much appreciated, extraordinary offer.

    Reply

  4. Zwembaden West-Vlaanderen’s avatar

    I’m in astonishing spirits that you in a general sense offered this staggering information to us

    Reply

  5. Webdesign’s avatar

    I should concede that your post is truly fascinating. I have spent a great deal of my extra time perusing your substance. Much thanks to you a ton!

    Reply

  6. Zonnepanelen’s avatar

    Stunning, glad to see this amazing post. I trust this think help any amateur for their great work. By the path a debt of gratitude is in order for offer this greatness from

    Reply

  7. Warmtepomp’s avatar

    It’s outstandingly a confusing and key bit of information.
    I’m in stunning spirits that you in a general sense offered this astonishing information to us

    Reply

  8. Zonnepanelen kopen’s avatar

    Incredible Information sharing .. I am glad to peruse this article .. a debt of gratitude is in order for giving us experience info.Fantastic decent. I welcome this post.

    Reply

  9. russianarmysurplus’s avatar

    It’s exceptionally a confounding and key piece of data.
    I’m in shocking spirits that you in a general sense offered this surprising data to us

    Reply

  10. Prijs airco’s avatar

    It’s outstandingly a bewildering and key bit of information.
    I’m in stunning spirits that you in a general sense offered this astonishing information to us

    Reply

  11. Warmtepomp’s avatar

    This site is momentous data and certainties it’s extremely brilliant

    Reply

  12. Kinderkleding’s avatar

    Astounding sharing, much obliged for imparting to the online network.

    Reply

  13. Airconditioning’s avatar

    This is really a viable and lovely data for all. A debt of gratitude is in order for sharing this to us and more power

    Reply

  14. Chape’s avatar

    It’s remarkably a befuddling and key piece of data.
    I’m in staggering spirits that you in a general sense offered this astounding data to us

    Reply

  15. Erotische massages’s avatar

    It’s surprisingly a bewildering and key bit of information.
    I’m in stunning spirits that you in a general sense offered this shocking information to us

    Reply

  16. Tuinarchitect’s avatar

    very informatic content i knew so much intresting things from your site content

    Reply

  17. Renovatie tuin’s avatar

    It is incredibly pleasant to see the best subtleties displayed in a simple and getting way.

    Reply

  18. Striptease’s avatar

    Interesting data, this is something new for me

    Reply

  19. Chiazaad’s avatar

    It’s shockingly a stupefying and key piece of data.
    I’m in staggering spirits that you in a general sense offered this stunning data to us

    Reply

  20. Noix’s avatar

    I found your blog website on the web and appearance a couple of your initial posts.
    Continuously keep inside the generally excellent work. I simply additional expansion your Rss to my MSN News Reader.
    Searching for toward perusing significantly more by you down the line

    Noix

    Reply

  21. Airco’s avatar

    Hey, I find examining this article a delight.
    It is staggeringly useful and fascinating and particularly imagining analyzing a progressively critical proportion of your work..

    Airco

    Reply

  22. Meisjeskleding’s avatar

    Dynamite works of art! That is the sort of data that are intended to be shared over the web. Disgrace on web indexes for never again situating this post higher! Please finished and examine with my site . Much obliged to you =)

    Reply

  23. Jongenskleding’s avatar

    Wow! a mind blowing post man. Much obliged to you Nonetheless I’m encountering issue with ur rss . Don’t have a clue why Struggling to buy in to it. Perhaps there would anyone say anyone is getting indistinguishable rss trouble? Anyone who realizes generously react.

    Reply

  24. Warmtepompen’s avatar

    A debt of gratitude is in order for setting aside the effort to talk about this, I feel emphatically about it and love adapting more on this point.

    Reply

  25. Terrasoverkappingen ’s avatar

    Hi. I discovered your blog utilizing msn. This is an incredibly elegantly composed article.
    I will make sure to bookmark it and come back to peruse a greater amount of your valuable data.
    Much obliged for the post. I’ll absolutely rebound.

    Reply

· 1 · 2

© 2008-2019 BI: Beer Intelligence? All Rights Reserved