Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DoEvents with SQL Server 2000 and Extended Procedures


DoEvents with SQL Server 2000 and Extended Procedures

Author
Message
Herve Roggero
Herve Roggero
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
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
JteL
JteL
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7225 Visits: 2679
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
tomad35
tomad35
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?



Herve Roggero
Herve Roggero
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
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
Jonr
Jonr
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 65
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) BigGrin

Jon Reade.

Jon Reade


Jon
Herve Roggero
Herve Roggero
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search