How to call a Webservice 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 webservice 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 Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.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:\drop\FlashPNLWSClient\FlashPNLWSClient.dll'
WITH PERMISSION_SET = UNSAFE
go


CREATE ASSEMBLY [FlashPNLWSClient.XmlSerializers]
FROM 'F:\drop\FlashPNLWSClient\FlashPNLWSClient.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 [domain\user], true
GO

use master
GRANT UNSAFE ASSEMBLY TO [domain\user]
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()

Back to C#