SQL Server Performance Forum – Threads Archive

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=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

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)

Hi Dinesh,
From SP use extended SPs like sp_OACreate to create the instance of com component.
sp_oamethod to call any inbuilt method you created in COM and
sp_oadestroy to destroy the com instance.
Ill post the sample asap.

… 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=’

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. 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!!!

]]>