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

extended procedures

I'm finding lots of code samples for writing extended procs, but *all* of
them
do nothing with SQL. Hello or calculating free disk space are common
examples.
I'm writing a sp because I want to work with SQL data. How do I do
that from an xp?
Do I have to go back around through ADO?
I would think that ODS would have a method in the API to interact with
the data, but you can't tell that from the sample code I've found so far.
Thanks,
Brad.Normally an XP will perform functions that aren't available (or aren't fast
enough) via standard T-SQL. If all you want to do is execute standard T-SQL
statements against your database, you probably want to write a regular SP.
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23v1TOVwYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> I'm finding lots of code samples for writing extended procs, but *all* of
> them
> do nothing with SQL. Hello or calculating free disk space are common
> examples.
> I'm writing a sp because I want to work with SQL data. How do I do
> that from an xp?
> Do I have to go back around through ADO?
> I would think that ODS would have a method in the API to interact with
> the data, but you can't tell that from the sample code I've found so far.
> --
> Thanks,
> Brad.
>
>

Extend the sql server management studio

I'm looking for info/documentation about writing plugins for the management studio.
i'd like to extend the interface, for instance, alter the interface to allow scripting of all indexes for a table, by adding that option to the table context menu.

i'm also looking for a way to create macros in the query editor, so i can have pressing tab after writing a short word expand to a pre-selected template.

any help appreciated.

Check the following URL....

add-in to SQL Server Management Studio

http://www.codeproject.com/useritems/enisey.asp?df=100&forumid=315231&exp=0&select=1908869

|||http://blogs.msdn.com/dtjones/archive/2005/11/16/493440.aspx you can as per Dan's blog out there, I believe your best bet would be to contact Microsoft dev.team for more information.

expression for regional report

Hi all thnaks in advacne for your time im new to MS reprting and am
writing my first report albeit very slow but im getting there is there
a way for me to
for instance i am reporting on a number of sales in different
countries so i would like the total sales to show as =A3 for uk sales
and the euro sign for spain, france, italy etc?
i can determine the county but just need to know if i can change the
currency?
CheersOn May 25, 9:58 am, blueboy <matt_me...@.hotmail.com> wrote:
> Hi all thnaks in advacne for your time im new to MS reprting and am
> writing my first report albeit very slow but im getting there is there
> a way for me to
> for instance i am reporting on a number of sales in different
> countries so i would like the total sales to show as =A3 for uk sales
> and the euro sign for spain, france, italy etc?
> i can determine the county but just need to know if i can change the
> currency?
> Cheers
I don't think you can have multiple currencies because the currency is
represented based on Locale:
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices=
.aspx#CurrencySetting
If you can find a way to put multiple locals, or you have data for
different countries on separate tables then you can probably change
the currency. You may want to try an IIF command. In that link, it
shows you how to change the locale. You can use an expression in
there. For the formula you can use something like
=3DIIF(FIELD!<Name of your field>.value=3D'UK',English (United
Kingdom),English(United States))
So if the value for the field that identifies the record as one from
the UK is true, it will format it as british pounds, otherwise it will
format it as US $. You want more than one country, just nest IIF
commands as such:
=3DIIF(FIELD!<Name of your field>.value=3D'UK',English (United
Kingdom),IIF(FIELD!<Name of your field>.value=3D'USA',English(United
States), Arabic (Egypt)))
etc... it gets complicated after a few permutations. Try it with a
simple IIF like I first demonstrated and see how it goes. I have a
complex nested IIF in one of my GO TO URL buttons for selected between
3-4 different reports and passing parameters so I know it works.
Note: my syntax might not be perfect but I believe the concept is
correct and you can always find sources on here that will show you how
to properly write IIF commands.
Hope this helps!