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:

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

Add a Stored Procedure to your project:

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:

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.

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.

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

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.

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:

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 = EXTERNAL_ACCESS;
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.