Call ActiveX component from T-SQL

Hi,
Does any one have a code to call activeX dll from T-sql. I couldn't find any thing from the web.

Something like


CREATE FUNCTION dbo.GetDriveSize (@drive CHAR)
RETURNS DECIMAL(38,0)
BEGIN
DECLARE @result INTEGER
DECLARE @objFSO INTEGER
DECLARE @drv INTEGER
DECLARE @cDrive VARCHAR(13)
DECLARE @size VARCHAR(20)

SET @cDrive = 'GetDrive("' + @drive + '")'
EXEC @result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @result = 0
EXEC @result = sp_OAMethod @objFSO, @cDrive, @drv OUTPUT
IF @result = 0
EXEC @result = sp_OAGetProperty @drv,'TotalSize', @size OUTPUT
IF @result<> 0
SET @size = NULL

EXEC sp_OADestroy @drv
EXEC sp_OADestroy @objFSO

RETURN @size
END
GO
SELECT dbo.GetDriveSize('C')
DROP FUNCTION dbo.GetDriveSize

???

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

hmm
can you tell me this

I have a dll called TestCOM.dll which has hte interface of TestCOM.TestClass
it has the function AddTwoNumbers(i, j) -- returns numbers added
can you tell me how to call this function

Sorry! I haven't played around that much with the sp_OA* procedure to know how to pass input parameters to the DLL. You might be able to use OASetProperty, but then again, I'm not sure on this. I would search the Google groups and the web. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

As far as functionality goes, you can do anything you like - read/write files, send network messages, call OLE/COM functions, kick off other programs, run ftp's.

Use the SQL OLE Automation calls to call a COM object - sp_OAxyz See Q152801 and the books-online for details of sp_OACreate, sp_OAMethod, sp_OAGetProperty and sp_OASetProperty.

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

thankx Frank and Satys I will give a try!

Btw, have you considered writing your own Extended Stored Procedure?
I mean if you are familiar with C, C++ or Delphi, this might be an option.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

... whatsoever method you use make sure to test and deploy carefully as they are tend to be flaky and can affect SQL server.

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Hehe, you can also wait until November. With the CLR integration, those things should be getting easier. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Yes even I suggested this to the customer. But answer was "How Can I Trust the words of Microsoft"

Absolutely, until unless you have the full product in hand and tested you cannot trust the M$.
Again another waiting time to fix the bugs or service pack release. I don't think customers will be ready to upgrade SQL 2000 to 2005 instantly after the release.

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Yes Satya, in that aspect I think MS has not gained good image. <br /><br />Moreover,Customers are also bit worried about the pricing factor too![<img src='/community/emoticons/emotion-6.gif' alt='' />]

I guess I am therehttp://www32.brinkster.com/srisamp/sqlArticles/article_31.htm will update as soon as I get the work done

Thank you all for the guidance. I was able to it!!!