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#

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-1) was last changed on 24-May-2017 15:30 by UnknownAuthor
G’day (anonymous guest) My Prefs
  • View Page Source
  • This clear IPSec security association,
    clear ipsec sa peer X.X.X.X
    

All Pages

Page views: 1982

Private Tomcat

Linux

MySQL

Email

SQL Server

ASP

JSP

C#

Web Mail

Windows Plesk

Linux Plesk

PHP

Gaming

ASP.NET

Persits ASPUpload

Wiki Help

Referring Pages:
...nobody

JSPWiki v2.8.1