Calling a web service from SQL Server

Recently I had to call a web service from SQL Server for integrating with a phone system that only allowed interaction with third-party systems via a database. It seemed straightforward enough given that we can write CLR functions in SQL Server. It ended up taking me a few hours to get all the settings correct before I could make it work.

I started off with this excellent article by bennie which saved me at least a couple of days of work

http://footheory.com/blogs/bennie/archive/2006/12/07/invoking-a-web-service-from-a-sqlclr-stored-procedure.aspx It's a little old but most of the steps were still the same.

Here are some of the things I ran into that would be useful for anyone doing the same,

1.       If you are using Visual Studio 2010, you do not need to do the post-build event for sgen.exe, instead in the build properties for the project set the "Generate serialization assemblies" to "On"

2.       After you run the alter on the database to set the TRUSTWORTHY option to on, you may get an error message The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXXX'. You should correct this situation by resetting the owner of database 'XXXXX' using the ALTER AUTHORIZATION statement.  If this happens you just need to do as the instruction says and run the alter authorization statement to set the db owner to be a login on your server that you trust.

<p class="MsoListParagraph">
  <i></i>
</p>

<p class="MsoListParagraph" style="">
  <i><span style="">3.<span style="font: 7.0pt Times New Roman;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></i>In the manual deployment script, you need remember that the path given for the file of the assembly is a path on the database server and not on your local machine, so you may want to setup a post-build script to either copy the files to the server or use UNC paths and then set the path appropriately in your deployment file.<i></i>
</p>

<p class="MsoListParagraph">
  <i></i>
</p>

<p class="MsoListParagraph" style="">
  <i><span style="">4.<span style="font: 7.0pt Times New Roman;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></i>If you get an exception when running the proc that <i>"There was an error generating the XML document. -> System.Security.SecurityException: That assembly does not allow partially trusted callers.". </i>Then you need to set the AllowPartiallyTrustedCallers assembly attribute in your assemblyInfo.cs file and deploy the assembly.<i></i>
</p>

<p style="font-size: 10px;">
  <a href="http://posterous.com">Posted via email</a> from <a href="http://sijin.posterous.com/calling-a-web-service-from-sql-server">Sijin Joseph</a>
</p>