QL Server comes with a powerful language called Transact-SQL (T-SQL) for writing queries and stored procedures. T-SQL is a full-featured language. Like any programming language, it has command statements, conditional logic, and a variety of functions. But what if T-SQL doesn’t have a function that you need? Or what if you have data that you need in SQL Server but is only accessible through a Visual Basic component (DLL)? This is where the SQL Server OLE automation stored procedures can save the day. In this 10-Minute Solution I cover these various stored procedures and how you can use them to access an object’s properties. (I’ll cover calling an object’s methods in a future article.)

SQL Server has seven stored procedures that allow you to create and use ActiveX objects from within SQL Server (see Table 1). These seven stored procedures are actually extended stored procedures and are contained in the stdole70.dll file. Here we’ll look at sp_OACreate, sp_OAGetProperty, and sp_OADestroy.

In order to call an object’s methods or properties, that object must first be registered on the server. To register an object, call the regsvr32 executable from the command prompt with the specified file name like this: regsvr32 objectname.dll.

To access any registered ActiveX components from inside SQL Server, your first step is to create the object using sp_OACreate and passing it the ProgID of the object you wish to create. This stored procedure has a return value that indicates its success in creating the object.

The following code uses the Application object from the Microsoft Word object model to demonstrate the use of these stored procedures. Specifically, this code returns the username for the currently installed version of Word. This code can be copied into Query Analyzer and run as long as you have Word installed on the server:

DECLARE @Object intDECLARE @Hresult intDECLARE @Username varchar(255)DECLARE @ErrorSource varchar (255)DECLARE @ErrorDesc varchar (255)--Create the objectEXEC @Hresult = sp_OACreate 'Word.Application', @Object OUT --Call the object's property and return the valueEXEC @Hresult = sp_OAGetProperty @Object, 'UserName', @Username OUT--Destroy the object EXEC @Hresult = sp_OADestroy @ObjectIF @Hresult <> 0BEGIN  EXEC sp_OAGetErrorInfo @Object, @ErrorSource OUT, @ErrorDesc OUT  PRINT "Error Occurred Calling Object:  " + @ErrorSource + " " + @ErrorDesc  RETURNENDPRINT @Username

When you use the OLE Automation stored procedures, the first thing you need to do is create variables for the return value and the object. In this case, the @Object variable holds a reference to Word’s Application object. Once the object is created using sp_OACreate, the sp_OAGetProperty stored procedure uses that object reference and calls the specified property. The value of the UserName property is returned to the @UserName variable. This variable must be declared with the appropriate datatype that is returned by the property. The sp_OASetProperty procedure works in the same way but takes a parameter with the value to set the property to.

If an error is encountered, sp_OAGetErrorInfo returns the error information for the last OLE automation stored procedure call. This stored procedure returns the error number, error source, error description, help filename, and help context ID for the current error. The above code uses only the error source and description values.

This gives you a summary of how to use these stored procedures to create an ActiveX object and access its properties. For more detailed information on these procedures and their parameters see the SQL Server BOL.