August 25, 2002 at 12:00 am
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
August 26, 2002 at 12:25 am
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.
August 26, 2002 at 6:03 am
Interesting. Good to see some content that pushes the envelope!
Andy
August 26, 2002 at 12:28 pm
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?
August 26, 2002 at 1:59 pm
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
August 27, 2002 at 8:11 am
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
August 28, 2002 at 8:48 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply