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