Executing a VB.NET DLL From SQL Server

Objective:

we want to write a DLL using VB.NET 2008 using .Net 3.5 that performs an HTTP request

we then want to call the DLL From SQL Server 2005 (in a trigger) and pass the DLL some variables and get a return value.

Where im at in the process.

I have created the DLL 'HTTPDLL.dll'

Source:

Imports System
Imports System.Configuration
Imports System.Collections.Generic
Imports System.Text
Imports System.Net
Imports System.Security
Imports System.Security.Permissions
Imports System.IO
Imports Microsoft.SqlServer.Server



Public Class HTTPDLL

Public Function HTTPPost(ByVal URL As String, ByVal Content As String) As Boolean

    Dim myURL As String = Trim(URL)
    Dim mycontent As String = Trim(Content)
    Dim rawOutput As String = ""

    'Get Acces Right to web
    Dim p As New WebPermission(NetworkAccess.Connect, myURL)
    p.Assert()

    'Prepare the web Request
    Dim req As WebRequest = WebRequest.Create(myURL)
    req.Timeout = 60000
    req.Method = "POST"
    req.ContentLength = mycontent.Length
    req.ContentType = "application/x-www-form-urlencoded"

    Dim sw As New StreamWriter(req.GetRequestStream())
    sw.Write(mycontent)
    sw.Close()

    Dim resp As WebResponse = req.GetResponse()
    Dim sr As New StreamReader(resp.GetResponseStream)
    rawOutput = sr.ReadToEnd()
    sr.Close()
    Return True


End Function

Public Function Test() As Integer
    Return 1
End Function


Public Function DllMain(ByVal hInst As Long, ByVal fdwReason As Long, _
ByVal lpvReserved As Long) As Boolean

    Dim s As String =     "sdfghjkolihdgjkhgiueghkyhekygigdjhgfkhsgdkhfgksjdhgkjshdgfkjhsgdkjfhgkshdgfkjhgskjdhgfkjhsdgkfhgskjdhfgkjsdhgfkshdgfkhgsdkfhgksdhfgkshdgfkshdgfkjhsgdkfhgskdhfgksjdhgfkjshgdfkhsgdkfhgskdhfgkshgdfkjhgskdjg"
    s &= "kjhdgjkshkdjfhklsjdhfljhgkhvbfiuvbli klrfgliu ghliebliuhdflivbdkljhgljuhfvliuhdf"
    Return True

End Function

End Class

I have placed the DLL in the SQL Server c:/Windows/System folder

I have loaded these assemblies

HelloWorld System.Core System.XML.Linq

using these commands

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure
go

EXEC sp_configure 'clr enabled' , '1'
go

sp_configure 'Ole Automation Procedures', '1'
GO 
RECONFIGURE

-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go
reconfigure
GO
-- assembly style
ALTER DATABASE master SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [System.Xml.Linq]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll'
WITH PERMISSION_SET = UNSAFE
GO

create assembly HelloWorld from 'C:\WINDOWS\system\HTTPDLL.dll'
with permission_set = safe
GO

I then Added the Extended Procedure

USE [master]
GO
/****** Object:  ExtendedStoredProcedure [dbo].[HTTPDLL]    Script Date: 02/03/2010 11:45:28 ******/
EXEC dbo.sp_addextendedproc N'HTTPDLL', 'C:\WINDOWS\system\HTTPDLL.dll'

(at this point im not sure why or if i needed to add the assembly, since this references the Dll directly.)

What I need solved. I now want to call the DLL's (extended procedure) HTTPPost Function

What I have Tried exec HTTPPost "Ms开发者_C百科g 17750, Level 16, State 0, Procedure HTTPPost, Line 1 Could not load the DLL HelloWorld, or one of the DLLs it references. Reason: 126(The specified module could not be found.)."

exec HelloWorld.HTTPPost 'http://www.somewebsite.com',''

"Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'HelloWorld.HTTPPost'."

What I need: If you can look over my implimentation and see if i am setting everything up properly, and also tell me (with examples please) how to call that DLL properly, sending it variables and catching its return value. I will be very gratefull.

I have looked through MANY posts/articles and have got bits and peices that have taken me this far, but nothing has been all inclusive.

I have also tried this

-- Scratch variables used in the script
DECLARE @retVal INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)

-- Initialize the COM component.
EXEC @retVal = sp_OACreate 'HTTPDLL.HTTPDLL', @comHandle OUTPUT
IF (@retVal <> 0)
BEGIN
  -- Trap errors if any
  EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
  SELECT [Error Source] = @errorSource, [Description] = @errorDescription
  RETURN
END

(and other such variants) and get this in the return of sp_OAGetErrorInfo

"Error Source Description ODSOLE Extended Procedure Invalid class string"

Perhaps i am calling it incorrectly but i havent been able to get it to work (always return 'invalid class string')

Thank you in advance for your help!.

You created a CLR Assembly which is different than a Unmanaged Extended Stored Procedure, or a OLE Automation COM object. You were somewhat on track up to the point of adding the Extended Stored Procedure. You don't do that here. You need to create a Stored Procedure using CREATE PROCEDURE DDL syntax from the Assembly. Take a look at the source and creation scripts for my article on SQL Server Central:

http://www.sqlservercentral.com/articles/SQLCLR/65657/

You will need External Access rights on the Assembly, it can't be created with SAFE and make a web service call like that. In addition to this, adding this functionality as a trigger is a bad idea. If there is a problem with the webservice and you generate an unhandled CLR exception, it can cause transactional failure and rollback in the trigger. In addition to that if there is latency in the webservice call you will delay the completion of your transaction in SQL which can cause blocking and slow response times.

A better solution to this would be to use a Service Broker Queue and Notification Service with the External Activator to activate an external process to work the queue asynchronously and outside of SQL Server. All the trigger has to do is send a message to the queue and it is done. This way if there is latency in the service call or the service is down the information is still stored and if you build your activation app correctly it will transactionally process the queue so a failure would keep the information in queue for retry later.

Please take a look at this http://dbalink.wordpress.com/2008/10/25/cannot-load-the-dll-error-in-sql-server-2005/

That is probably what you need ;-)

ACPerkins @ Experts-exchange has enlightened us with this article Worst Practice - Triggering External Events http://www.sqlservercentral.com/articles/Triggers/worstpracticetriggeringexternalevents/1283/

and as such we are currently looking into some other methedologies. thanks for your help. Anthony