external stored procedure (DLL) in Java?

Hi,

I am going to be writing an external stored procedure (my first) for SQL server 2000.

Has anyone out there written a DLL in Java (J++ or .NET) and then accessed the functions within the DLL as an external stored procedure in T/SQL?

I ask the question becaues I'm likely to get it done considerably faster if I write it in Java then VB ;-)

Any advice / suggestions most welcome.

Cheers,

EwanIt shouldn't really make a difference what language you write your dll in. As long as its compiled as a dll, you should be able to call it from a Stored Proc.

Just ensure that the dll is registered on the server that as executing the StoredProc (not the client).

As far as I know , there is no way to return values from the dll into the Stored Proc. Please let me know if there is.

I take no credit for the information below. I copied it from an old posting and saved it, and I cannot remember who posted it originally.

Good luck.

Lionel.

You can do it with the SP_OA* extened stored procedures, located in the
MASTER database (of ms-sql 7.0/2000). Look at this example (for sending
email
through jmail);

CREATE PROCEDURE sp_Send_JMail

@.fromName as char(50),
@.fromEmail as char(50),
@.toName as char(50),
@.toEmail as char(50),
@.subject as char(100),
@.Body as char(500)

AS
DECLARE @.ObjTok int
DECLARE @.RetVal int

EXEC @.RetVal=sp_OACreate'JMail.SMTPMail',@.ObjTok OUT
EXEC sp_OASetProperty @.ObjTok, 'ServerAddress','yourmailserver.com'
EXEC sp_OASetProperty @.ObjTok, 'SenderName', @.fromName
EXEC sp_OASetProperty @.ObjTok, 'Sender', @.fromEmail
EXEC sp_OASetProperty @.ObjTok,'Subject', @.Subject
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'AddRecipient', Null,@.toEmail
EXEC sp_OASetProperty @.ObjTok, 'Body', @.Body
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'Execute'
EXEC sp_OADestroy @.ObjTok GO