DoEvents with SQL Server 2000 and Extended Procedures

  • Herve Roggero

    Ten Centuries

    Points: 1230

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hroggero/doevents.asp

    Herve Roggero
    hroggero@pynlogic.com
    MCDBA, MCSE, MCSD
    SQL Server Database Proxy/Firewall and Auditing

  • JteL

    SSC Enthusiast

    Points: 121

    I don't think the performance overhead is as large as the article suggests.

    When @iNBR_DOEVENTS = 0 the IF statement that checks to see if the extended stored procedure should be executed is not evaluated. Surely in such a simple procedure this would have a large implact.

  • Andy Warren

    SSC Guru

    Points: 119684

    Interesting. Good to see some content that pushes the envelope!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • tomad35

    SSC Rookie

    Points: 35

    I find it interesting that you don't even mention the use of sp_OACreate, which is SQL Server's OLE Automation Extended Stored Procedures. We have been using this since SQL 6.5 to call custom dll's that perform just about anything you can imagine.

    What would be the advantage of doing it your way?

  • Herve Roggero

    Ten Centuries

    Points: 1230

    Yes you are right. There are many ways to skin a cat. And sp_OACreate is definitively a good alternative.

    To answer your question, I prefer not to use sp_OACreate when I can since it requires me to manage the object instanciation and destruction.

    Finally, sp_OACreate goes effectively through a proxy (a call to odsole70.dll) that instanciates and calls the desired DLL for you. The approach I am proposing is a little more lightweight: no object creation/destruction to worry about, and a more direct call to the DLL.

    However, I will agree with you that sp_OACreate is a more comprehensive (and complete) way to call more complex methods, and may even be required in certain instances. I will give it a try to see if I there is a difference in performance.

    Thanks for your feedback.

    Herve Roggero
    hroggero@pynlogic.com
    MCDBA, MCSE, MCSD
    SQL Server Database Proxy/Firewall and Auditing

  • Jonr

    SSCrazy

    Points: 2566

    Very interesting article Herve, though I found it tweaking my curiosity from another angle.

    Spent a few days leisurely trying to coerce VB(6) into creating a 'special' extended stored proc DLL which SQL Server could call, as a registered, external stored procedure, just as you're doing here. But eventually my efforts came to a halt as it appeared that it would be necessary to either create an instance of the DLL using sp_OACreate (which I could implement, but didn't want to do for various reasons), or write a DLL with the header that SQL Server expects using Visual C++ (which I've not got).

    Question is....does SQL Server 2000 interface with 'normal' DLLs (unlikely)

    -or-

    is MSVBVM60.DLL 'SQL Server ready' with the header.

    Or is it simply that I'm being dim? (probably) 😀

    Jon Reade.

    Jon Reade


    Jon

  • Herve Roggero

    Ten Centuries

    Points: 1230

    Thank you Jon. Good questions - let me try to answer them.

    Actually, it seems that SQL Server can interact with normal DLLs. However, the DLL's functions have to be exported in order for them to work within SQL. VB does not seem to export its public functions when you compile a custom DLL, so that's why you can't use your functions as extended stored procedures. So you pretty much have to code in C to create an exported function.

    Microsoft recommends you export a function (__GetXpVersion) returning the ODS version, which requires the ODS headers... since I didn't see this function inside the MSVBVM60.DLL file, I assume that the VB virtual machine does not contain references to the SQL Server headers.

    But I don't believe you need the SQL Server ODS headers if you do not plan to interact with SQL Server from within the code. That's why I can call a MSVBVM60-exported function.

    You can find the C headers (and samples) on the SQL Server CD. They do not install automatically, but are under the DEVTOOLS directory.

    Good luck

    Herve

    Herve Roggero
    hroggero@pynlogic.com
    MCDBA, MCSE, MCSD
    SQL Server Database Proxy/Firewall and Auditing

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply