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_OASetProperty

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_OAGetProperty

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

Ken

 

[Adlai Stevenson]

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_OASetProperty

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_OAGetProperty

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

Ken

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]

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