When I wanted to connect a web service and perform some tasks in database at regular intervals of time, my first choice was going for windows service. That was easy and I was familiar with the process. Due to some constraints in my organization, I had to look for alternatives.I was able to access the webservice from sql clr.Steps:1. Create a wsdl class file of the webservice.using in command prompt
wsdl http://www.test.com/WebService/Testsvc.asmx /language:vb /out:C:\TestSvc.vb Include this class file in the application where you are creating a sql clr. Below is an example: Imports Microsoft.VisualBasic Imports System Imports System.data Imports System.Data.Sql Imports System.Data.SqlClient Imports Microsoft.SqlServer.Server Imports System.Data.SqlTypes Imports System.Runtime.InteropServices Imports System.XmlPartial Public Class StoredProcedures_ Public Shared Sub GetTest( ByRef value1 As SqlInt32, ByRef value2 As SqlString) Dim Connection As New SqlConnection("Context Connection = true") Dim iWeb As New com.WSMainTestDim result as Integer = iWeb.GetAdditionTest(value1, value2)Connection.Open() Dim insert As String = "Insert into tblResponse (Text) Values ('" + result + "')" Dim command1 As New SqlCommand(insert, Connection) command1.ExecuteNonQuery() Connection.Close() Connection.Dispose() End Sub End Class3.
Once I created the clr built the application and turned this class into a dll and also web service is also created as dll. create sgen for webservice.4.Add all 3 dlls to the database on which you wanted to attach the sql and access the webservice. Get to the database, in the assembly folder add these 3 dlls. The main clr file which connects to dll need to have external access permission set.5.Once you add these 3 dlls to the assembly folder, create procedure for the stored procedure clr.
Create PROC [Stored Procedure definition]([Parameters]) With EXECUTE AS CALLERASEXTERNAL NAME (dll name of the stored procedure). (class name).(method name)
For my purpose I created a job to perform certain tasks at regular intervals of time.Hope this could help someone.Thanks,Kishore Patil