Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

Extending to support Visual Basic etc 

The final XP in XP_RUNSCIPT is XP_RUN_ACTIVEX and allows you to implement an XP in any development environment, which can produce a standard ActiveX DLL. The most obvious example for this would be Visual Basic 6. Here we use COM’s late bound interface “IDispatch” to directly run a specified function in a ActiveX / COM DLL with a specified ProgID. The code for this XP is implemented in the class “CRunActiveXStoredProcedure” which is also derived from “CRunScriptStoredProcedure” like the two script XP’s already discussed. This XP takes two parameters, the first parameter is the ProgID of the COM object to call and the second parameter is the function in that COM object to run. Since we do not have the concept of a “Named item” in a non-script language, we instead insist that the function we call have a specific syntax. In Visual Basic you need to write code of the following format: 

Function Function1(SQL As XPServer) As Boolean

  SQL.SendInfoMsg "Hello from Visual Basic"

  Function1 = True

End Function 

Note that to be able to use object types such as XPServer and all the constants for the ODS data type defined in XP_RUNSCRIPT, you need to add a reference to the XP_RUNSCRIPT type library. You can do this in Visual Basic 6 using the standard “Project -> References” Menu item and checking the tick box for “XP_RUNSCRIPT 1.0 Type Library”. You should return True from the function to indicate success and False to indicate a failure. This will become the return value from the XP via “CExtendedStoredProcedure::Run”. By naming the parameter as “SQL”, you can make the VB code look pretty much the same as the equivalent VBScript code for no extra effort. 

The code in “CRunActiveXStoredProcedure::Run” can be broken down into the following steps: 

Intialize COM using the “CCOMInitialize” class just like the other 2 XP’s in XP_RUNSCRIPT. 

Obtain the 2 parameters for the XP using standard XP++ code.

Initialize the “m_pParametersCOMArray” array for the object model wrappers for the parameters array. 

Initialize a CComVariant with a pointer to the SQLServer object. This will become the “SQL” object in the “Function1” example above.

Given the ProgID of the COM object to run, convert this to a COM CLSID via the function CLSIDFromProgID.  

Create the COM object using CoCreateInstance. We use the built in ATL class “CComDispatchDriver” to encapsulate this object.

Call “IDispatch::GetIDsOfName” via “CComDispatchDriver” to convert the function name we want to run into a COM DISPID. A DISPID is a simple numeric identifier for a function. 

Call the function in the COM object via “CComDispatchDriver.Invoke1”. We pass the SQLServer object as the single parameter.

Examine the return value from the COM object and return this back as the return value. 

That completes the core code for the “CRunActiveXStoredProcedure” class. The final piece of code required is to expose this class as an XP from the DLL. This is achieved using the following code in the module “XP_RUNSCRIPT.cpp”: 

IMPLEMENT_XP_RUNSCRIPT(XP_RUN_ACTIVEX, CRunActiveXStoredProcedure); 


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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

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