How To Call A web service From SQL Server - Knowledge Base by Hyve Managed Hosting

How to call a web service from SQL Server

Create a web service client using a c# assembly, create a new class library
Remove the namespace from the class file as this causes complications
Change Classname to suit
Create a web reference to the web service you wish to call
Create the function name you wish to call, e.g. edit the following code to suit, you may wish to encrypt the username and password in the configuration properties file

	using Microsoft.SqlServer.Server;
	using System.Net;
	....

 	private static NetworkCredential GetCredential() { 
            NetworkCredential credential = new NetworkCredential("XXX", "XXX", "XXX");
            return credential;
        }

        [SqlProcedure
        public static string SendEmailReminders()
        {
            try
            {
                EmailReminder reminder = new EmailReminder();
                reminder.Timeout = System.Threading.Timeout.Infinite;
                reminder.Credentials = GetCredential();
                reminder.SendEmailReminder();
                return "ok";
            }
            catch (Exception e) {
                return e.Message;
            }
        }

Add a new post-build event on the command line, right click on project, choose build events, make sure the path to sgen.exe is correct

"c:Program FilesMicrosoft Visual Studio 8SDKv2.0Binsgen.exe" /a /force $(TargetPath)

Build the program and you should see 3 files output, these are a config, a DLL and a XmlSerializers.dll file
Copy the 3 files onto your SQL Server database server ready to import into SQL Server

Run the following commands in SQL

–Enabled CLR in SQL Server EXEC sp_configure @configname = ‘clr enabled’, @configvalue = 1 GO RECONFIGURE WITH OVERRIDE GO –Make database trust these Assemblies ALTER DATABASE DBNAME SET TRUSTWORTHY ON RECONFIGURE GO

Now create the assemblies in SQL Server, change the code to suit.

CREATE ASSEMBLY [FlashPNLWSClient
FROM 'F:dropFlashPNLWSClientFlashPNLWSClient.dll'
WITH PERMISSION_SET = UNSAFE
go


CREATE ASSEMBLY [FlashPNLWSClient.XmlSerializers
FROM 'F:dropFlashPNLWSClientFlashPNLWSClient.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE
go

You may need to change the owner of the database if you restored or copied the DB and sync the users with the logins

EXEC sp_changedbowner [domainuser, true
GO

use master
GRANT UNSAFE ASSEMBLY TO [domainuser
GO

Create function in SQL Server to call the webservice

CREATE FUNCTION SendEmailReminders() returns NCHAR(4000)
AS EXTERNAL NAME FlashPNLWSClient.[FlashPNLWSClientClass.SendEmailReminders
go

Now run the function in SQL Server.

print dbo.SendEmailReminders()