Category: SQL SQL Server

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()

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.