SQL Server Performance

Call ActiveX component from T-SQL

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by dineshasanka, Aug 9, 2005.

  1. dineshasanka Moderator

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

  2. FrankKalis Moderator

    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)
  3. dineshasanka Moderator

    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
  4. FrankKalis Moderator

    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 />
  5. satya Moderator

    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.
  6. dineshasanka Moderator

    thankx Frank and Satys I will give a try!
  7. FrankKalis Moderator

    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)
  8. ranjitjain New Member

    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.
  9. satya Moderator

    ... 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.
  10. FrankKalis Moderator

    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 />
  11. dineshasanka Moderator

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

    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.
  13. dineshasanka Moderator

    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=':(' />]
  14. dineshasanka Moderator

  15. dineshasanka Moderator

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

Share This Page