Calling COM Objects From Sproc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calling COM Objects From Sproc

Author

Topic

ereader
Yak Posting Veteran

50 Posts

Posted - 2003-04-17 : 10:14:54

Hi ALL

I am having the .NET DLL
named->>> SQLProjectNET
Class->>> SQLClassNET

and have a
method->> AddNet(Adding simply two numbers)

and going to invoke the functionality of the .NET DLL
the same functionality and the proc working fine with the VB 6.0 is there is any special provision for the .NET dll

-- .NET

DECLARE @object int
DECLARE @hr int
DECLARE @return INT
EXEC @hr = sp_OACreate 'SQLProjectNET.SQLClassNET', @object OUT
PRINT @HR
EXEC @hr = sp_OAMethod @object, 'AddNET', @return OUT ,3,15
PRINT @HR
PRINT @return
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

#########################################
but it's giving me the following error

-2147221005
-2147211483

Error Source Description HelpFile HelpID
---------------------------------------------------------
0x80042728 ODSOLE Extended Procedure sp_OADestroy usage: ObjPointerToBeDestroyed int IN. NULL 0

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-17 : 12:16:46

The .Net DLL is not COM, so sp_OACreate will not run it.
If you are just adding two numbers together, there are several ways to do that in T-SQL.

Here's one way


DECLARE @Num1 INT
DECLARE @Num2 INT
DECLARE @Results INT


SET @Num1 = 10
SET @Num2 = 3

SELECT @Results = @Num1 + @Num2


SELECT @Results as MyResult

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ereader
Yak Posting Veteran

50 Posts

Posted - 2003-04-17 : 12:25:15

quote:

The .Net DLL is not COM, so sp_OACreate will not run it.
If you are just adding two numbers together, there are several ways to do that in T-SQL.

Thanks

but it's not a matter of adding two number this was just a example i am looking for how to execute the .net dll

any way
thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-17 : 13:40:57

You have a few options:
1. Dump .Net and write VB6 / VC++ 6 COM DLL
2. Write a VB6 / VC++ 6 COM DLL that calls your .Net object.

Bottom line, you need a COM objec to call using sp_OAcreate.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

handarohit
Starting Member

14 Posts

Posted - 2004-05-18 : 05:21:07

Hey I m facing the same problem with VB COM dll. Any help ?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-18 : 07:30:21

You can create a COM wrapper around .NET:

http://www.csharphelp.com/archives3/archive513.html
http://www.dnzone.com/ShowDetail.asp?NewsId=126

or, scrap sp_oa methods and access the CLR natively using the following extended proc (xp_exec_clr_named_proc):

http://www.turtlenecksoftware.com/default.aspx?section=0

handarohit
Starting Member

14 Posts

Posted - 2004-05-18 : 08:39:44

But I am not working on .NET platform. Actually I have created a COM DLL:- VMSalerts.dll and class name is :- clsVMS alerts and in my SQL server proc I am doing something like :-
EXEC @hr = sp_OACreate 'VMSalerts.clsVMSAlerts', @alert OUT
this returns @hr as -2147211483
instead of 0 as in successful case
Please try to help me out of this

wharden
Starting Member

4 Posts

Posted - 2004-05-19 : 16:24:06

Actually you can use .NET with a COM Callable Wrapper. Add a COM class template to your project and delete the default Class Library file, and Visual Studio does most of the work for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources