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 "Msg 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!.