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. Robert’s avatar

    Thank you very much!
    This is the first post that i actually got working!

    Best regards
    Robert

    Reply

  2. Valentino Vranken’s avatar

    Thanks Robert, I’m glad that it helped you out :-)

    BTW: I have one more item that I’m planning to add to the article (once I find the extra minutes to work it out), about configuration through the sqlservr.exe.config file.

    Reply

  3. Ben’s avatar

    Great work!

    This is exactly what I need to get my SQL database communicating with Navision!

    Would never have been able to figure this out without your article.

    Reply

  4. Steve’s avatar

    Great and simple article – Well done!

    Could you please explain how I can make this SP scheduled using SQL Server Agent?

    Reply

  5. Valentino Vranken’s avatar

    Thanks for the comment Steve :-)

    The SP can be scheduled using the “Transact-SQL script (T-SQL)” as Type in the Job Step. Then use a command like “EXEC your_custom_sproc”.

    Reply

  6. Raman’s avatar

    Hi Valentino,

    I’m getting the following error:

    —————————————-
    A .NET Framework error occurred during execution of user-defined routine or aggregate “usp_InvokeTestWebService”:

    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.
    ——————————————

    Any help is much appreciated.

    Raman

    Reply

  7. Raman’s avatar

    Never mind.

    I had some time to look at it today and found this link that had the goods: http://support.microsoft.com/kb/913668 (Microsoft Knowledge base article 913668).

    I ran into a different problem, but that is a different story.

    Excellent post from you, though. Got me started.

    Raman

    Reply

  8. Valentino Vranken’s avatar

    Hi Raman, I’m glad you worked it out and thanks for providing the link to the solution, saves me the time to look into it :-)

    Cheers,
    Valentino.

    Reply

  9. bobalue’s avatar

    nice post, very helpful, but i get

    Msg 6505, Level 16, State 2, Procedure testGetAddress, Line 19
    Could not find Type ‘StoredProcedures’ in assembly

    when I try to create the stored procedure, any thoughts?

    Reply

  10. venkatesh’s avatar

    i want to simmilar code to work for my trigger invoked

    Reply

  11. Larry, Seattle WA’s avatar

    i already have a web service running on a machine.

    how can i access one of the methods of that web service from a sqlserver SP running on a different machine….

    Reply

  12. Alessandro’s avatar

    Hi, I have a problem; after attending to your post, when I deploy with visual studio I have this error:

    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.

    Connection to database is created with ‘sa’ operator, I run scripts on this post for solving this problem but it doesn’t work…

    Any suggestion?

    Thanks, ciao

    Reply

  13. Valentino Vranken’s avatar

    @Larry: the method described in this article supports calling a web service located anywhere. As long as you have access to it, it should work just as it’s explained. Just add your web service to the .NET project and produce the .NET code to call your web method.

    Reply

  14. Valentino Vranken’s avatar

    @Alessandro: it’s not sufficient to run the script while being logged on as ‘sa’. As explained above in “Owner needs external access”, the owner of the database in which you are creating the CLR Procedure needs to have EXTERNAL ACCESS ASSEMBLY permission.
    One solution to this problem is making ‘sa’ the owner of your database. That’s done with the following command: EXEC sp_changedbowner ‘sa’, false;

    To find out who the current owner of your database is, right-click the database in the Object Explorer (Management Studio) and choose Properties. The owner is mentioned on the General page.

    Have a look here to find out how to create a login (the DB owner is a login) that has EXTERNAL ACCESS ASSEMBLY permission: http://msdn.microsoft.com/en-us/library/ms345106.aspx

    (Hmm, something seems to be going wrong with my blog engine. I wanted to past the code from that site reference but for some reason I can’t. Anyway, it’s located about halfway down.)

    Hope that helps you out?

    Regards,
    Valentino.

    Reply

  15. Jeff’s avatar

    I am trying to post some question not letting me!

    Reply

  16. Marcus’s avatar

    Hi,
    Just a slight amendment.. the MyStoredProcedures.XmlSerializers.dll can be added with the SAFE permisson rather than EXTERNAL_ACCESS, as the elevated access is only required for the dll that’s calling outside the SQL CLR sandbox.

    Regards
    Marcus

    Reply

  17. Valentino Vranken’s avatar

    Thanks for adding your observation Marcus, I appreciate that! And it indeed makes sense :-)

    Regards,
    Valentino.

    Reply

  18. Irene’s avatar

    Thanks for a great example! :-)

    Reply

  19. Dan’s avatar

    I am running into the same error as Bobalue – in my case I am creating a UDF instead of an SP, but otherwise I am following the example. Bobalue – did you ever resolve your issue? Does anyone else have any suggestions?

    Is there a way to see what objects are being recognized in the registered assembly?

    Thanks, Dan

    Reply

  20. Terry’s avatar

    Excellent example, thanks.

    Reply

  21. SAMIR’s avatar

    I’m getting following error on executing Stored Procedure-

    Msg 6522, Level 16, State 1, Procedure usp_GetWeatherByZipCode, Line 0
    A .NET Framework error occurred during execution of user defined routine or aggregate ‘usp_GetWeatherByZipCode’:
    System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. —> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. —> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
    System.Net.Sockets.SocketException:
    at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
    at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
    System.IO.IOException:
    at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
    at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size)
    at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead)
    System.Net.WebException:
    at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
    at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at WebServiceFromDB.com.cdyne.ws.Weather.GetCityWeatherByZIP(String ZIP)
    at StoredProcedures.WeatherSP(String zipCode)
    .

    (1 row(s) affected)

    Reply

  22. SQL learning’s avatar

    Wow good thing I found your blog. I’m a student and I’m working for a website using apsx with SQL server 2005! thank you thank you!

    Reply

  23. Mehdi’s avatar

    thanks, very clear and very useful.

    but I have a question. I want to set my web service url dynamically. as you mentioned about sqlserv.exex.config. but when I use your code to access read the configuration file using ConnectionManager, it is not defined in my C# project. how can I use ConectionManager in the project. I also wrote in using part the following code:
    Using system.configuration

    but it does not work.

    it would be appreciated if help me to solve the problem.

    Thanks and Regards.

    Reply

  24. tory burch boots sale’s avatar

    thanks ,it is useful for me right now.
    it confused me for a long time.

    Reply

  25. Johnny’s avatar

    This article does make this whole process very easy.
    I would like to know is it possible to suppress the number of records that are affected from being displayed like when you use SET NOCOUNT ON in standard stored procedure?

    Thanks

    Reply

    1. Valentino Vranken’s avatar

      Hi Johnny,

      According to the Books Online, the SET NOCOUNT option “stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set”.

      As we’re calling the custom stored proc using T-SQL, I would think that the setting would indeed also do its thing here. Unfortunately I’m not able to try this out at the moment, maybe you can confirm? :)

      Regards,
      Valentino.

      Reply

      1. Johnny’s avatar

        Hi Valentino,

        You were spot on.

        Thanks

        Reply

        1. Valentino Vranken’s avatar

          Good to know, thanks for reporting back! :)

          Reply

  26. Johnny’s avatar

    Hi Valentino,

    I have another issue that I am struggle ling to get the answer for.
    I create the stored procedure and assemblies and everything works fine.
    When the process is accessed I get the following error, that disappears as soon
    as I recreate procedures and assemblies.

    Error in task sql: A .NET Framework error occurred during execution of user-defined routine or aggregate “np_mq_send_mail”:
    System.TypeInitializationException: The type initializer for ‘AmendmentsSQL.wsAmendmentProcess’ threw an exception. —> System.NullReferenceException: Object reference not set to an instance of an object.
    System.NullReferenceException:
    at AmendmentsSQL.wsAmendmentProcess..cctor()
    System.TypeInitializationException:
    at AmendmentsSQL.wsAmendmentProcess..ctor()
    at AmendmentsSQL.StoredProcedures.SendMail(String toAddress, String fromAddress, String subject, String messageBody)
    .

    Thanks,
    Johnny

    Reply

  27. Joachim’s avatar

    Great blog. Thank you. Very easy to understand.

    Reply

  28. hosein’s avatar

    tank you for help
    very very helpful

    Reply

  29. Danilo Bucheli’s avatar

    I have the following error after run the :
    (Microsoft SQL Server Enterprise Edition (64-bit) + Windows Server 2008 R2 + Visual Studio Proffesional 2010)

    CREATE ASSEMBLY MyStoredProcedures
    FROM ‘C:\Basura\MyStoredProcedures\MyStoredProcedures\bin\Debug\MyStoredProcedures.dll’
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
    GO

    Assembly ‘MyStoredProcedures’ references assembly ‘system.servicemodel, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.’, which is not present in the current database.

    SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

    Any help will be appreciated.
    Thanks a lot

    Reply

    1. Valentino Vranken’s avatar

      Apparently the following assembly cannot be found on the server: system.servicemodel. As far as I can tell, that’s a new namespace as of .NET 3.0. Are you sure the server has got .NET 3.5 activated? (not the case by default according to http://social.technet.microsoft.com/Forums/en-US/windowsserver2008r2webtechnologies/thread/ac1bf719-0a88-4b5d-be98-395f2623e805)

      Hope that helps?

      Reply

  30. alphapapalima’s avatar

    Great blog. The configuring of the Stored Procedure was what I was looking for. My problem is that when I use a sqlservr.exe.config file and restart the SQL Server (2012), it never restarts correctly. I have to delete the file and restart SQL Server so it will run. Using the file, crashes SQL Server 2012. Any ideas?

    Reply

  31. Marmar’s avatar

    thank you so much for this post

    Reply

  32. Joy’s avatar

    Great article!!! Thank you very much for your post!!!

    Reply

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