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

Exporting to PDF in Java with web interface issue

Hi, thanks for taking the time to take a look at this.

As you can read from the title, I'm trying to export a report to pdf from a thick java client. My connection string is:
"http://10.0.1.36/ReportServer/?/Invoice1/Invoice&assetNbr=14
&invoiceId=061000002&rs:Command=Render:&rs:Format=PDF&rc:Toolbar=false"
(I just added the toolbar=false part, and it didn't fix the problem)

After getting the datastream I just throw it into a file and it works fine... Until I add an image to my report. I don't get any errors in my program, but when I try to open up the pdf itself I get the error: "There was an error opening this document. The file is damaged and could not be repaired."

What confuses me is that if I enter the connection string in ie it comes up and asks me what I should do with the pdf and that one has the image in it with not problems.

To summarize if I've been unclear:
Export via ie no image: good
Export via ie with image: good
Export via java no image: good
Export via java with image: error

Does anyone have an idea why this would occur? I have an Execution Account set up in Reporting Services Configuration Manager and I have IIS anonymous access set up to use my local administrator account so I don't think accessing the image is an issue. But I'm definitely no server/admin expert so if there are any such issues could be causing this don't assume I've got that set up properly.

Thanks, JeffAre you certain that you are writing the file correctly from your Java app?|||I'm pretty sure I am. The whole setup works and exports correctly when connecting to a report that doesn't have an image embedded in it.

Also, is there any way to access the reports other than the web interface from java? I've only seen implementations of this in C# and vb.net|||

You should be able to use any Java SOAP toolkit (such as JAX).

FWIW, perhaps the report w/the image is larger, and exposing some bug in your Java code? Maybe you should compare the byte count of the IE output to what you are getting w/Java.

|||My face is red, it just was that I was stopping the fileout one byte too early... Thanks a bunch for your help.

If I can ask one more favor of you, I'm not familiar with the components involved in using SOAP so if you could give me a little overview that would give me someplace to start reading about it I'd really appreciate it.|||

I've never used any of SOAP toolkits for Java... so I can't really help you out there. This showed some links which may be of interest to you:

http://search.live.com/results.aspx?q=java+SOAP+toolkit&mkt=en-US&form=QBRE