Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DoEvents with SQL Server 2000 and Extended Procedures Expand / Collapse
Author
Message
Posted Sunday, August 25, 2002 12:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 6, 2009 11:47 PM
Points: 48, Visits: 52
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
Post #6389
Posted Monday, August 26, 2002 12:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2003 12:00 AM
Points: 9, Visits: 1
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.




Post #40342
Posted Monday, August 26, 2002 6:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 7:34 AM
Points: 6,804, Visits: 1,933
Interesting. Good to see some content that pushes the envelope!

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




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #40343
Posted Monday, August 26, 2002 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 24, 2003 12:00 AM
Points: 1, Visits: 1
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?




Post #40344
Posted Monday, August 26, 2002 1:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 6, 2009 11:47 PM
Points: 48, Visits: 52
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
Post #40345
Posted Tuesday, August 27, 2002 8:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:47 AM
Points: 142, Visits: 64
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) :D

Jon Reade.

Jon Reade



Jon
Post #40346
Posted Wednesday, August 28, 2002 8:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 6, 2009 11:47 PM
Points: 48, Visits: 52
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
Post #40347
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse