Invoking .NET dll from SQL

[Ken Sturgeon]

We have an existing ASP application that is supported by SQL 2000. We plan
on utilizing a third party COM object for which we'll be writing a wrapper.
Initial thoughts are to write a VB6 COM wrapper around the 3rd party dll but
we'd like to start a slow migration to .NET; I say slow because we don't
have funding to rewrite the entire app so we're hoping to use .NET on all
new functionality if possible.

We'll be calling the object from our SQL box by using the sp_OA* system
stored procedures offered by SQL Server to invoke the wrapper, which in turn
will invoke the 3rd party COM object. I know this can be accomplished if we
write our wrapper in VB6 but my ultimate question is... Can I do the
following?

1. Inovoke a .NET dll from SQL stored procedure sp_OACreate

2. Change a property in a .NET dll using sp_OASetPropert y

3. Execute a subroutine within the .NET dll using sp_OAMethod

4. Pass property values from the .NET dll to the 3rd Party COM object.

5. Update property values in the .NET dll based on output from the COM
object

6. Get the updated property values from the .NET dll using sp_OAGetPropert y

I'm not sure how I might better explain the process... any insight is
greatly appreciated.

Ken

[Adlai Stevenson]

Re: Invoking .NET dll from SQL

Ken Sturgeon wrote:
[color=blue]
> We have an existing ASP application that is supported by SQL 2000. We plan
> on utilizing a third party COM object for which we'll be writing a wrapper.
> Initial thoughts are to write a VB6 COM wrapper around the 3rd party dll but
> we'd like to start a slow migration to .NET; I say slow because we don't
> have funding to rewrite the entire app so we're hoping to use .NET on all
> new functionality if possible.
>
> We'll be calling the object from our SQL box by using the sp_OA* system
> stored procedures offered by SQL Server to invoke the wrapper, which in turn
> will invoke the 3rd party COM object. I know this can be accomplished if we
> write our wrapper in VB6 but my ultimate question is... Can I do the
> following?
>
> 1. Inovoke a .NET dll from SQL stored procedure sp_OACreate
>
> 2. Change a property in a .NET dll using sp_OASetPropert y
>
> 3. Execute a subroutine within the .NET dll using sp_OAMethod
>
> 4. Pass property values from the .NET dll to the 3rd Party COM object.
>
> 5. Update property values in the .NET dll based on output from the COM
> object
>
> 6. Get the updated property values from the .NET dll using sp_OAGetPropert y
>
> I'm not sure how I might better explain the process... any insight is
> greatly appreciated.
>
> Ken
>
>[/color]

Well -- at the very worse, you could always create a console app that
calls the dll for you.

Then you can use the Execute Command stored procedure, run the console
..exe and pass the variables for properties that you want to update as
command line arguments.

[Ken Sturgeon]

Re: Invoking .NET dll from SQL

That's a great idea Adlai. If I do that would I be able to get the output
params passed back to the calling stored procedure? I don't believe that
would be possible. Fortunatly in this particular situation I may have a work
around for that issue.

Thanks very much for your time and your insight.

Ken

"Adlai Stevenson" <mstowel@boy.to wel.spam> wrote in message
news:yZhJc.3168 $Qu5.1057@newsr ead2.news.pas.e arthlink.net...[color=blue]
> Ken Sturgeon wrote:
>[color=green]
> > We have an existing ASP application that is supported by SQL 2000. We[/color][/color]
plan[color=blue][color=green]
> > on utilizing a third party COM object for which we'll be writing a[/color][/color]
wrapper.[color=blue][color=green]
> > Initial thoughts are to write a VB6 COM wrapper around the 3rd party dll[/color][/color]
but[color=blue][color=green]
> > we'd like to start a slow migration to .NET; I say slow because we don't
> > have funding to rewrite the entire app so we're hoping to use .NET on[/color][/color]
all[color=blue][color=green]
> > new functionality if possible.
> >
> > We'll be calling the object from our SQL box by using the sp_OA* system
> > stored procedures offered by SQL Server to invoke the wrapper, which in[/color][/color]
turn[color=blue][color=green]
> > will invoke the 3rd party COM object. I know this can be accomplished if[/color][/color]
we[color=blue][color=green]
> > write our wrapper in VB6 but my ultimate question is... Can I do the
> > following?
> >
> > 1. Inovoke a .NET dll from SQL stored procedure sp_OACreate
> >
> > 2. Change a property in a .NET dll using sp_OASetPropert y
> >
> > 3. Execute a subroutine within the .NET dll using sp_OAMethod
> >
> > 4. Pass property values from the .NET dll to the 3rd Party COM object.
> >
> > 5. Update property values in the .NET dll based on output from the COM
> > object
> >
> > 6. Get the updated property values from the .NET dll using[/color][/color]
sp_OAGetPropert y[color=blue][color=green]
> >
> > I'm not sure how I might better explain the process... any insight is
> > greatly appreciated.
> >
> > Ken
> >
> >[/color]
>
> Well -- at the very worse, you could always create a console app that
> calls the dll for you.
>
> Then you can use the Execute Command stored procedure, run the console
> .exe and pass the variables for properties that you want to update as
> command line arguments.
>[/color]