Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter

sp_OACreate, sp_OAMethod etc


Built into SQL Server 2000 are a number of functions which allow you to break out from TSQL and into the world of COM. “sp_OACreate” allows you to create a COM object, “sp_OADestroy” destroys a COM object when you are finished with it, “sp_OAGetProperty” and “sp_OASetProperty” allow you to set or get standard COM properties, and finally “sp_OAMethod” allows you to call a method in a COM object. A couple of additional methods namely “sp_OAGetErrorInfo” and “sp_OAStop” help in error handling and cleanup. These functions work as advertised and allow a measure of encapsulation of some of the logic of a stored procedure but the issue is that TSQL is entirely in control and the COM object is called by TSQL. In an ideal environment what you would like is to allow all the flexibility of the ODS API, such as returning recordsets and reading and writing parameters from the COM object code itself. This approach means that the object calls back into SQL Server at the appropriate time and has the flexibility to do what it wants when it wants. In addition this approach would reduce the amount of TSQL boilerplate code which otherwise would be required by using the “sp_OA*” functions.


The Solution


Ok, so the solution to these issues would be to allow script languages such as VBScript or JScript to create stored procedures. We also would like to provide a better solution than the “sp_OA*” functions so that we could write a complete stored procedure in a development environment such as Visual Basic 6. Providing this level of functionality would mean that we could open up the world of Extended Stored Procedures to a whole new audience who are not or cannot get their head around the minutiae of C++ development. These two problems are distinct but can be addressed by a common code base, which we will develop in this chapter.


The approach we will take is to develop a COM wrapper for the ODS API. The wrapper should implement a simple object model, which rationalizes the ODS API just like ASP provides an object model using objects such as “Request”, “Response”, “Server” etc. This DLL should also provide all the richness of the ODS API so that we can do things such as returning recordsets of specific data types and query and set its parameters.

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter  


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter