Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002

    Wink Unanswered: external stored procedure (DLL) in Java?


    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.



  2. #2
    Join Date
    May 2002
    It 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.


    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
    through 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)

    DECLARE @ObjTok int
    DECLARE @RetVal int

    EXEC @RetVal=sp_OACreate'JMail.SMTPMail',@ObjTok OUT
    EXEC sp_OASetProperty @ObjTok, 'ServerAddress',''
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts