APssing params to COM object with T-SQL ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

APssing params to COM object with T-SQL ??

Author

Topic

helmut_news
Starting Member

2 Posts

Posted - 2006-03-13 : 08:19:36

Hi

I'm runnign a solution based upon SQL Server 2000 and I urgently need to fetch data through an existing COM object. I need to call up a Visual basic Sub inside a .dll file and I'm having problem making the call from my SQL stored procedure.

The SQL code underneath is what I have done so far (based upon an article I found)..I reaaly dont know how to pass on all my three mandatory parameters..in the DLL file the GetPrice sub takes 6 parameters, three of these are my mandatory inparameters, the other three parameters are only used for passing ByRef values back from the sub...how can I handle these inputs/outputs when making the call from my SQL stored procedure..the structure of my DLL GetPrice sub is
Sub GetPrice(p1, p2, p3, out1, out2, out3, out4)

-- Scratch variables used in the script
DECLARE @retVal INT
DECLARE @comHandle INT
DECLARE @retString VARCHAR(100)

-- Initialize the COM component.
EXEC @retVal = sp_OACreate 'MyDLL.GetPrice', @comHandle OUTPUT
-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'GetPrice', ???????????????

How can I make a call from the stored procedure that will handle this situation. Very greatful for help on this one...

Thanks
Helmut

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-13 : 08:58:16

Have a look at
http://www.nigelrivett.net/DMO/SQL-DMOTransfer.html
it gives an example of caling a com object from t-sql, setting properties and calling methods with parameters.
You need a return code value (null if not required) after the object name.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helmut_news
Starting Member

2 Posts

Posted - 2006-03-13 : 10:07:02

Hi..and thanks for the answer...

the problem is that I cant set any properties in the DLL, the only thing I can do is to call the Visual Basic sub :

GetPrice(p1,p2,p3,p4,p5,p6,p7) where p1-p3 will contain my inparams and p4-p7 will be set by the DLL ....I have tried to call the DLL like this:

DECLARE @p1 varchar(100)
DECLARE @p2 varchar(100)
DECLARE @p3 varchar(100)
DECLARE @p4 varchar(100)
DECLARE @p5 varchar(100)
DECLARE @p6 varchar(100)
DECLARE @p7 varchar(100)

set @P1 = 'paramvalue1'
set @P2 = 'paramvalue2'
set @P3 = 'paramvalue3'
set @P4 = ''
set @P5 = ''
set @P6 = ''
set @P7 = ''
EXEC @retVal = sp_OAMethod @comHandle, 'GetPrice', NULL, @p1, @p2 ,@p3 ,@p4,@p5 ,@p6 ,@p7

..and then just print the @p4 value that should have ben set by the DLL....but obviously I do something wrong since this is not working...appreciate some further hints

BR
Helmut

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources